在某些应用场景中,我们经常会遇到一些排名的问题,比如按成绩或年龄排名。排名也有多种排名方式,如直接排名、分组排名,排名有间隔或排名无间隔等。
1.创建测试表
CREATE TABLE IF NOT EXISTS Scores(
Id INT PRIMARY KEY,
Score FLOAT NOT NULL
)Engine=Innodb;
INSERT INTO Scores VALUES(1,3.50);
INSERT INTO Scores VALUES(2,3.65);
INSERT INTO Scores VALUES(3,4.00);
INSERT INTO Scores VALUES(4,3.85);
INSERT INTO Scores VALUES(5,4.00);
INSERT INTO Scores VALUES(6,3.65);
2.排名
2.1 普通排名
按分数高低直接排名,从1开始往下排,出现一行数据统计一行,类似于row number。
SELECT id, score, @curRank := @curRank + 1 AS `rank` FROM scores, (SELECT @curRank := 0) r ORDER BY score desc;
申明了一个变量 @curRank ,并将此变量初始化为0,查得一行将此变量加一,并以此作为排名。查询结果如下:
score | rank |
---|---|
4 | 1 |
4 | 2 |
3.85 | 3 |
3.65 | 4 |
3.65 | 5 |
3.5 | 6 |
2.2 分数相同,名次相同,排名有间隔
select t.score,(select count(s.score)+1 from scores s where s.score>t.score) `rank` from scores t order by t.score desc;
两张表,统计右表大于左表分数出现的次数+1。查询结果如下:
score | rank |
---|---|
4 | 1 |
4 | 1 |
3.85 | 3 |
3.65 | 4 |
3.65 | 4 |
3.5 | 6 |
2.3 分数相同,名次相同,排名无间隔
SELECT t.score,(SELECT count( s.score ) + 1 FROM( SELECT s.score, count( s.score ) FROM scores s GROUP BY score ORDER BY score DESC ) s WHERE s.score > t.score ) `rank` FROM scores t ORDER BY t.score DESC;
在有间隔的基础上,利用分组,过滤掉重复的分数,查询结果如下:
score | rank |
---|---|
4 | 1 |
4 | 1 |
3.85 | 2 |
3.65 | 3 |
3.65 | 3 |
3.5 | 4 |
3.函数
mysql 8.0 版本后出现的函数,可以利用 ROW_NUMBER(),DENSE_RANK(),RANK() 三个窗口函数实现上述三种排名。
select id,score, ROW_NUMBER() OVER(order by score desc) as `rank` from scores; #普通排名
select id,score, DENSE_RANK() OVER(order by score desc) as `rank` from scores; #分数相同,名次相同,排名无间隔
select id,score, RANK() over(order by score desc) as `rank` from scores; #并列排名,排名有间隔