Mysql rank() row_number() dense_rank()区别

这三个函数均 MySQL 5.8x 以上版本，以下版本会报语法错误，属于正常现象。

select version()查看数据库版本

数据库原始数据

select stu_no,
score,
rank() over( ORDER BY a.score desc) as number1,
row_number() over( ORDER BY a.score desc) as number2,
dense_rank() over( ORDER BY a.score desc) as number3
from score a ORDER BY score DESC

result

应该是很直观了

over 里面加上 PARTITION by 结果如下

select stu_no,
score,
rank() over(PARTITION by stu_no ORDER BY a.score desc) as number1,
row_number() over(PARTITION by stu_no ORDER BY a.score desc) as number2,
dense_rank() over(PARTITION by stu_no ORDER BY a.score desc) as number3
from score a ORDER BY stu_no

所以当我们想取出 在stu_no 相同下 的score的最大组

SELECT * from 
(
select stu_no,
score,
rank() over(PARTITION by stu_no ORDER BY a.score desc) as number1
from score a ORDER BY stu_no
) a WHERE a.number1 <2

多个条件进行排序

   order by a.score DESC,age
     （多条件排序，还有条件可以再加在后面）
     (不同条件排序规则，先按score降序排，再按age升序排)

根据一个条件多个值进行排序

order by
     case when status=1 then 0
             when status=0 then 1
             when status=4 then 2
             when status=3 then 3
             when status=2 then 4 ,
    add_time desc

意思是： 先根据用户状态排序： 状态为1的排在最前面 >其次是状态为0 >状态为4 >状态为3 >状态为2 ; 再根据添加时间降序排序。

参考链接
https://blog.csdn.net/weihuan2323/article/details/123488484