mysql 排名_mysql @rank:=@rank+1 的用法,例:对成绩排序并显示排名

表信息

1 -- 按各科成绩进行排序，并显示排名

set @rank = 0;
select sc.*,@rank := @rank + 1 from score sc
order by score;

2 -- 查询学生的总成绩并进行排名

错误的写法:

set @rank = 0;
select sc.*,sum(score) @rank := @rank + 1 from score sc
group by s_id
order by sum(score) ;

出错的原因: @rank := @rank + 1不能和group by 一起使用

解决办法: 将分组计算的结果作为一个虚拟表存在

正确写法:

set @rank = 0;
select a.* , @rank := @rank + 1 from 
(select sc.*,sum(score)from score sc
group by s_id
order by sum(score) desc) as a ;

结果: