简单排序
select a.*, (@rowNum:=@rowNum+1) rank
from a, (select (@rowNum :=0)) b
order by a.score desc
分组排序
根据能力标签分组,然后组内按分数排序
注:capability_id 是能力标签,score 是分数
select if(@c=capability_id,@r:=@r+1,@r:=1) as rank, @c:=capability_id capability_id, sc.*
from bde_student_capability sc, (select @c:=0,@r:=0)r
order by capability_id asc,score desc
重点:
- 分组排序得按组内来编号,所以每组就得有一个不变的列,用来group by,抓住这个特点自然就理解了rank的含义。
- group by的时候要有两个排序的条件,要不然组内会不稳定