MySQL8.0的三种排名函数

1.rank()

按照某字段的排序结果添加排名,但它是跳跃的、间断的排名,例如两个并列第一名后,下一个是第三名。

SELECT Score,rank() over(ORDER BY Score desc) as 'Rank' FROM score;
# 分组排序
SELECT Score,rank() over([partition by xxx] ORDER BY Score desc) as 'Rank' FROM score;

2.row_number()

它是将某字段按照顺序依次添加行号。

SELECT Score,row_number() over(ORDER BY Score desc) as 'Rank' FROM score;
# 分组排序
SELECT Score,row_number() over([partition by xxx] ORDER BY Score desc) as 'Rank' FROM score;

3.dense_rank()

dense 英语中指“稠密的、密集的”。dense_rank()是的排序数字是连续的、不间断。当有相同的分数时,它们的排名结果是并列的,例如,1,2,2,3。

SELECT Score,dense_rank() over(ORDER BY Score desc) as 'Rank' FROM score;
# 分组排序
SELECT Score,dense_rank() over([partition by xxx] ORDER BY Score desc) as 'Rank' FROM score;

4.总结

img