- rank()
按照某字段的排序結(jié)果添加排名，但它是跳躍的语淘、間斷的排名诲宇，例如兩個并列第一名后，下一個是第三名惶翻。
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;
- 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吕粗；
- dense_rank()
dense 英語中指“稠密的它掂、密集的”。dense_rank()是的排序數(shù)字是連續(xù)的溯泣、不間斷。當(dāng)有相同的分數(shù)時榕茧，它們的排名結(jié)果是并列的垃沦，例如，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.總結(jié)
對比分析rank(), dense_rank(), row_number()
- 案例
create table score(
Id INT NOT NULL PRIMARY KEY auto_increment,
Score decimal(5,2) NOT NULL
);
insert score(Score)
values(88),
(56),
(77),
(88),
(75),
(77),
(56),
(33);
select * from score;
select Id, Score, DENSE_RANK() OVER(ORDER BY Score DESC) AS `Rank`
from score;
dense_rank()應(yīng)用示例
