我试图回答另一个所以问题 https://stackoverflow.com/questions/18404726并突然面临以下问题。分数应分配给得分最高的 3 个(mrk
) 组 (grp
)每个班级(sec
)。得分最高的组得5分,排名第二的组得3分,排名第三的组只得1分。对于所有其他人pts
应设置为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) |
我选择使用用户定义的变量,因为它们在分配方案方面提供了最大的灵活性,并很快提出了以下解决方案:
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
and @m:=mrk
都进行评估,然后将它们的值进行比较=
。结果可以是0
(假)或1
(true) 但它肯定不等于5
,另一个论点NULLIF
函数,因此最终只有第一个参数(5
)将被退回。我选择的构造是为了在不返回任何内容的情况下进行两个变量赋值。
好吧,也许不是最直接的解决方案;-),但我确实注意到了对于正在处理的每个记录,每个变量仅定义一次,因为“涉及用户变量的表达式的求值顺序未定义”mysql手册 http://dev.mysql.com/doc/refman/5.0/en/user-variables.html. The select
确实给了我想要的
result:
| 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
?
到目前为止我的尝试都失败了:
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
result:
| 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 |
为什么更新语句只获得 pts 的单个值 (5)?!?
你可以在我的里面找到所有的数据和SQL语句SQL小提琴 http://sqlfiddle.com/#!2/b1461/6.