记使用MySQL的SQL语句对排名功能实现

遇到一个需求，需对所有学生分数进行排名，并且列出名次。

数据库准备

创建一个分数表s_score

CREATE TABLE `s_score`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `score` int NOT NULL DEFAULT 0,
  `name` varchar(20) CHARACTER SET utf8mb4 NULL,
  PRIMARY KEY (`id`)
);

 插入测试数据

INSERT INTO `s_score` (`name`, `score`) VALUES
('张三', 80),
('小明', 90),
('小红', 60),
('李四', 70),
('赵武', 80),
('梁晨', 87),
('小绿', 69),
('威廉', 69),
('大卫', 91),
('王五', 96),
('赵六', 96),
('小五', 80),
('小龙', 88);

普通实现

在MySQL8.0推出Rank排名函数RANK，完全支持这种需求，但是必须MySQL8.0 以上版本才支持这个特性。8.0以下的版本有什么方法实现呢，可以使用用户变量，记录名次。

用户变量：以”@“开始，形式为”@var_name“,以区分用户变量及列名。它可以是任何随机的，复合的标量表达式，只要其中没有列指定。下面写一个小例子，展示如何使用用户变量

SELECT @a := 1 AS a, @b := @a+1 AS b;

 执行结果

:= 是赋值的意思，与编程语言赋值有点区别。下面开始使用简单SQL实现RANK排名函数效果 

用户变量简单实现名次显示

SELECT name, score, @rank := @rank + 1 AS rated FROM s_score AS s, (SELECT @rank := 0) AS q ORDER BY score DESC;

 执行结果：

并排名次展示

当出现分数相同，并列排名，名次应该相同。可以使用一个temp变量来记录前一个分数值，判断前面分数是否与当前相等，相等直接返回上一个排名情况，否则排名+1。 

SELECT name, score, CASE WHEN @temp_score = score THEN @rank WHEN @temp_score := score THEN @rank := @rank + 1 END AS rated FROM s_score AS s, (SELECT @rank := 0, @temp_score := NULL) AS q ORDER BY score DESC;

执行结果：

并排名次跳过

如果出现并列排名，下一个名次将自动跳过，比如出现两个并列第一，91应该变成第三名了，名次和人数相对应。

SELECT name, score, rated FROM (
SELECT name, score, @rank := IF(@temp_score = score, @rank, @rank_incr) AS rated, @rank_incr := @rank_incr + 1,
 @temp_score := score FROM s_score AS s, (SELECT @rank := 0, @temp_rank := NULL, @rank_incr := 1) AS q ORDER BY score DESC) AS a

 执行结果：

使用SQL窗口函数 

窗口函数的基本语法如下：

SELECT 排序函数/聚合函数 OVER (<PARTITION BY …> 分区字段 ORDER BY 排序字段);

根据维基百科解释： 

窗口函数允许在当前记录之前和之后访问记录中的数据。窗口函数定义一帧或一列窗口，其中当前行周围具有给定的长度，并跨窗口中的数据集执行计算。可以这样理解，窗口就是数据集合，函数就是计算数据方法。 

partiton by是可选的。如果不使用partition by，那么就是将整张表作为一个集合，最后使用排序函数得到的就是每一条记录根据排序列的排序编号。

排序函数主要有rank()、dense_rank、row_number，他们主要区别：

• rank(): 对同一个字段排序，出现相同时，会并列排名，并且会出现排名间隙。

• dense_rank(): 对同一个字段排序，出现相同时，会出现并列排名，排名连续的

• row_number(): 对同一个字段排序，排名是联系的，即使出现相同，不会并列排名次

SELECT name, score, RANK() OVER(ORDER BY score DESC) AS rated, ROW_NUMBER() OVER(ORDER BY score DESC) AS line,
DENSE_RANK() OVER(ORDER BY score DESC) AS dense FROM s_score;

执行结果：