MySQL-学生成绩排名

在某些应用场景中，我们经常会遇到一些排名的问题，比如按成绩或年龄排名。排名也有多种排名方式，如直接排名、分组排名，排名有间隔或排名无间隔等。

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，查得一行将此变量加一，并以此作为排名。查询结果如下：

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。查询结果如下：

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;

在有间隔的基础上，利用分组，过滤掉重复的分数，查询结果如下：

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;       #并列排名，排名有间隔