如何在UPDATE语句中使用用户定义的变量? [英] How to use user-defined variables in an UPDATE statement?
问题描述
我试图回答另一个 SO问题，突然遇到了以下问题.应将点分配给每个类别(sec)的3个最高得分(mrk)组(grp).得分最高的组获得5分，第二组获得3分，第三组仅获得1分.对于所有其他pts，应将其设置为null.
I was trying to answer another SO question and was suddenly faced with the following problem. Points should be assigned to the 3 highest scoring (mrk) groups (grp) of each class (sec). The highest scoring groups get 5 points, the second ranking ones 3 points and the groups in 3rd rank only 1 point. For all others pts should be set to null.
| ID | SEC | GRP | MRK | PTS |
|----|-----|-----|-----|--------|
| 1 | cl2 | ge | 32 | (null) |
| 2 | cl1 | gb | 22 | (null) |
| 3 | cl1 | gd | 22 | (null) |
| 4 | cl1 | ge | 18 | (null) |
| 5 | cl2 | ga | 26 | (null) |
| 6 | cl1 | ga | 55 | (null) |
| 7 | cl2 | gb | 66 | (null) |
| 8 | cl2 | gc | 15 | (null) |
| 9 | cl1 | gc | 12 | (null) |
| 10 | cl2 | gf | 5 | (null) |
| 11 | cl2 | ge | 66 | (null) |
我选择使用用户定义的变量，因为它们在分配方案方面提供了最大的灵活性，并很快提出了以下解决方案:
I chose to work with user-defined variables as they provide maximum flexibility regarding the allocation scheme and soon came up with the following solution:
SELECT id,sec,grp,mrk,
CASE WHEN @s=sec THEN -- whenever there is a new class ...
CASE WHEN @m=mrk THEN @i ELSE -- issue the same points for
-- identical scorers, otherwise ...
CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2 -- store mrk in @mrk and
-- while @i>2 return points: 3 or 1 ...
ELSE @i:=null -- no points for the rest
END
END
ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk)) -- store sec in @s and mrk in @m
-- and return points: 5
END pts
FROM tbl ORDER BY sec,mrk desc
NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))的解释:
对表达式@s:=sec和@m:=mrk都进行求值，然后通过=比较它们的值.结果可以是0(false)或1(true)，但绝对不等于5(NULLIF函数的另一个参数)，因此最后只有第一个参数(
The expressions @s:=sec and @m:=mrk are both evaluated and then their values are compared by =. The result can either be 0 (false) or 1 (true) but it will definitely be unequal to 5, the other argument of the NULLIF function, therefore in the end only the first argument (5) will be returned. I chose the construct to make the two variable assignments happen without returning anything.
好的，也许不是最直接的解决方案;-)，但我要注意为正在处理的每个记录仅定义一次每个变量，因为用户变量未定义" mysql手册. select确实给了我想要的
OK, maybe not the most straightforward solution ;-), but I did pay attention to define each variable only once for each record that is being processed, since "the order of evaluation for expressions involving user variables is undefined" mysql manual. The select indeed gives me the desired
结果:
| ID | SEC | GRP | MRK | PTS |
|----|-----|-----|-----|--------|
| 6 | cl1 | ga | 55 | 5 |
| 2 | cl1 | gb | 22 | 3 |
| 3 | cl1 | gd | 22 | 3 |
| 4 | cl1 | ge | 18 | 1 |
| 9 | cl1 | gc | 12 | (null) |
| 7 | cl2 | gb | 66 | 5 |
| 11 | cl2 | ge | 66 | 5 |
| 1 | cl2 | ge | 32 | 3 |
| 5 | cl2 | ga | 26 | 1 |
| 8 | cl2 | gc | 15 | (null) |
| 10 | cl2 | gf | 5 | (null) |
现在，我的问题是:
我该如何写UPDATE语句，并将上述计算结果存储在pts列中?
How do I write an UPDATE statement along the same lines that will store the above calculated results in column pts?
到目前为止，我的所有尝试都失败了:
My attempts so far have all failed:
UPDATE tbl SET pts=
CASE WHEN @s=sec THEN
CASE WHEN @m=mrk THEN @i ELSE
CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2
ELSE @i:=null
END
END
ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))
END
ORDER BY sec,mrk desc
结果:
| ID | SEC | GRP | MRK | PTS |
|----|-----|-----|-----|-----|
| 6 | cl1 | ga | 55 | 5 |
| 2 | cl1 | gb | 22 | 5 |
| 3 | cl1 | gd | 22 | 5 |
| 4 | cl1 | ge | 18 | 5 |
| 9 | cl1 | gc | 12 | 5 |
| 7 | cl2 | gb | 66 | 5 |
| 11 | cl2 | ge | 66 | 5 |
| 1 | cl2 | ge | 32 | 5 |
| 5 | cl2 | ga | 26 | 5 |
| 8 | cl2 | gc | 15 | 5 |
| 10 | cl2 | gf | 5 | 5 |
为什么update语句只为pts获得一个单一值(5)?！?
Why does the update statement only get a single value (5) for pts?!?
您可以在我的 SQLfiddle 中找到所有数据和SQL语句.
You can find all the data and SQL statements in my SQLfiddle.
推荐答案
我已经尝试调试这种情况.
我在tbl表中添加了6个新列:b_s，b_m，b_i和a_s，a_m，a_i
b_ *-表示之前"，a_ *-表示之后"，
并且我将查询修改为:
I have tried to debug this case.
I've added 6 new columns to the tbl table: b_s, b_m, b_i and a_s, a_m, a_i
b_* - means "before", a_* - means "after",
and I've modified the query to:
UPDATE tbl SET
b_s = @s,
b_m = @m,
b_i = @i,
pts=
CASE WHEN @s=sec THEN
CASE WHEN @m=mrk THEN @i ELSE
CASE WHEN IF(@m:=mrk,@i,@i)>2 THEN @i:=@i-2
ELSE @i:=null
END
END
ELSE NULLIF(@i:=5,(@s:=sec)=(@m:=mrk))
END,
a_s = @s,
a_m = @m,
a_i = @i
ORDER BY sec,mrk desc
我的目的是记录表达式评估前后的变量值.
奇怪-我不知道为什么，但是似乎您在执行更新之前为所有变量赋值时，更新就会按预期工作.
比较这两个演示:
1-错误: http://sqlfiddle.com/#!2/2db3e4/1
2-很好: http://sqlfiddle.com/#!2/37ff5/1
My intent was to log values of variables before and after of the expression evaluation.
It's strange - I don't know why, but it seems that when you assign values to all variables before the execution of the update then the update works as expected.
Compare these two demos:
1 - wrong: http://sqlfiddle.com/#!2/2db3e4/1
2 - fine: http://sqlfiddle.com/#!2/37ff5/1
唯一的区别是更新之前的这段代码片段:
The only difference is this code fragment before the update:
set @i='alamakota';
set @m='alamakota';
set @s='alamakota';
某种魔术字符串":)
这篇关于如何在UPDATE语句中使用用户定义的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
