背景:在做年级成绩排名时,已将查询结果按照总分从高到低的顺序排列好,此时需要能在查询结果中自增顺序列,来代表年级排名,可以用(@rowNO := @rowNo+1) AS rowno方法,但有一个问题:每查询一次,自动生成的顺序列会在上一次最后的名次基础上增加,我需要每次查询都是从1开始增加,解决方法如下:
select date,classes,b.s_id,s_name, ( k1+k2 )as total,
(@rowNO := @rowNo+1) AS rowno
from biology as a ,student as b ,(select @rowNO :=0) b where a.s_id=b.s_id
order by total desc
即:在from后加上
(select @rowNO :=0) b