mysql @value := 用法

背景

有这么一张表，记录名字和分数，现在需要按照成绩排名，存在并列名次的情况

CREATE TABLE `test1` (
  `name` varchar(10) ,
  `sex` varchar(10) ,
  `score` int
) ;

insert into test1 select 'w1','1',97 ;
insert into test1 select 'w2','1',93 ;
insert into test1 select 'w3','1',93 ;
insert into test1 select 'w4','1',96 ;
insert into test1 select 'w5','1',95 ;
insert into test1 select 'w6','1',97 ;

 

解决方法

思路：按照score从大到小排序，第一行数据就是第一名，第二行就是第二名......需要判断当前行的score和上一行的score的大小用来区分是否排名

 

SELECT name,score,
CASE
WHEN @preScore = score   THEN @curRank 
WHEN @preScore := score  THEN @curRank:=@curRank +1
END AS `Rank`
 FROM test1,(SELECT @curRank := 0 AS curRank, @preScore := NULL AS preRank) i WHERE sex=1 ORDER BY score DESC;

细节说明

 @preScore 这种是变量声明，类似之前的set ，set @a = 1；

:=  赋值，@preScore := 1，表示给@preScore赋值为1；

(SELECT @curRank := 0 AS curRank, @preScore := NULL AS preRank) i 单独派生出一个表，记得要加别名，不然会包如下的错误

Every derived table must have its own alias -- 派生出来的表都要有一个别名

　　新增两列的表，一列是当前排名curRank，一个是上条记录的score值preScore，赋予默认值，curRank=0，preScore=null；

下面的操作类似linux中的awk操作

1. 按照score倒序排列，即score最大的一行，第一名的一条记录；

2. 先判断@preScore是否跟当前查询出来的score一致，因为默认@preScore为null，那不一致，不执行THEN后面的；

3. 进入到第二个WHEN，THEN中

4. 将当前查询出来的score=99赋值给@preScore，没有判断条件直接进入到THEN，@curRank=0+1，为1

5. 将@curRank 写为别名Rank=1

6. 第二条记录扫描

7. 先判断@preScore（此时为99）跟第二条记录的score（此时为89）对比，不相等，不执行THEN后面的数据；

8. 将当前查询出来的score=89赋值给@preScore，没有判断条件直接进入到THEN，@curRank=1+1，为2

9. 将@curRank 写为别名Rank=1

......

扫描完所有的记录后得到上表

case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

当有score一样的情况时，@preScore=score时，@curRank并没有做+1操作，所以就有了并列的情况