一、数据准备
sql脚本如下:
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2)); Truncate table Scores; insert into Scores (Id, Score) values ('1', '3.5'); insert into Scores (Id, Score) values ('2', '3.65'); insert into Scores (Id, Score) values ('3', '4.0'); insert into Scores (Id, Score) values ('4', '3.85'); insert into Scores (Id, Score) values ('5', '4.0'); insert into Scores (Id, Score) values ('6', '3.65');
简述:成绩表只有id和成绩两个字段
二、排名函数分类
rank()
作用:
按照某字段的排序结果添加排名,但它是跳跃的、间断的排名,例如两个并列第一名后,下一个是第三名
示例代码:
SELECT score,rank() over(ORDER BY score desc ) as ranked FROM scores;
结果:
+-------+--------+ | score | ranked | +-------+--------+ | 4 | 1 | | 4 | 1 | | 3.85 | 3 | | 3.65 | 4 | | 3.65 | 4 | | 3.5 | 6 | +-------+--------+ 6 rows in set
row_number()
作用:
它是将某字段按照顺序依次添加行号
示例代码:
SELECT score,ROW_number() over(ORDER BY score desc ) as ranked FROM scores;
结果:
+-------+--------+ | score | ranked | +-------+--------+ | 4 | 1 | | 4 | 2 | | 3.85 | 3 | | 3.65 | 4 | | 3.65 | 5 | | 3.5 | 6 | +-------+--------+ 6 rows in set
备注:
只是单纯的添加行号,和成绩字段无关联
dense_rank()
作用:
dense 英语中指“稠密的、密集的”。dense_rank()是的排序数字是连续的、不间断。当有相同的分数时,它们的排名结果是并列的,例如,1,2,2,3
示例代码:
SELECT score,dense_rank() over(ORDER BY score desc ) as ranked FROM scores;
结果:
+-------+--------+ | score | ranked | +-------+--------+ | 4 | 1 | | 4 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.5 | 4 | +-------+--------+ 6 rows in set
三、总结
函数名 | 关键词 | 作用 | 主要适用场景 |
rank() | 间断、 不连续 | 按顺序输出排名结果,每当出现一个并列结果,则下一个排名数字向后递增一位 | 在并列结果中,每个并列成绩都要占用一个名额的场景 |
dense_rank() | 连续 | 按数学输出排名的名次。当出现并列结果时,下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔” | 当排序结果存在并列成绩时,需要下一个名次于上一个名次是连续整数的情况。 |
row_number() | 行号 | 按顺序输出表的行号 | 需要输出表的行号时 |