关于sql:MySQL排名函数实现

本文出处MySQL排名函数实现
转载请阐明出处

当初有个需要对所有学生分数进行排名，并且列出名次。刚看到这个需要，我有点懵逼，齐全没有思路????，为什么难一点需要，我就不会做呢???? 去网上查问材料,把所有实现都列出来，全副都要学会。

数据库筹备

创立一个分数表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 a,@b:=@a+1 b

执行后果

:= 是赋值的意思，与编程语言赋值有点区别。上面开始展现应用简略SQL实现RANK排名函数成果

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

SELECT name,score, @rank:=@rank+1 `rank` from s_score s,(select @rank:=0) 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    `rank` from s_score s,(select @rank:=0,@temp_score:=NULL) q ORDER BY score desc

并排名次跳过

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

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

应用SQL窗口函数

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

select 排序函数/聚合函数 over (<partition by ...> 分区字段 order by 排序字段)

留神over 前面有一个空格的，这个语法有点蛋疼，我本人试了十几次才书写胜利。
依据维基百科解释：窗口函数容许在以后记录之前和之后拜访记录中的数据。窗口函数定义一帧或一列窗口，其中以后行四周具备给定的长度，并跨窗口中的数据集执行计算。能够这样了解，窗口就是数据汇合，函数就是计算数据办法。

partiton by是可选的。如果不应用partition by，那么就是将整张表作为一个汇合，最初应用排序函数失去的就是每一条记录依据排序列的排序编号。
排序函数次要有rank()、dense_rank、row_number，他们次要区别：

• rank(): 对同一个字段排序，呈现雷同时，会并列排名，并且会呈现排名间隙。
• dense_rank() : 对同一个字段排序，呈现雷同时，会呈现并列排名，排名间断的
• row_number(): 对同一个字段排序，排名是分割的，即便呈现雷同，不会并列排名次

    select name,score, RANK() over (ORDER BY score DESC) `rank`,ROW_NUMBER() over (order by score DESC) `row`,    DENSE_RANK()over (ORDER BY score DESC) `dense` from s_score    

以上就是排序名次全副实现形式了，还有其余实现形式，麻烦在评论里补充一下。

参考文档

https://cloud.tencent.com/developer/article/1562954
https://www.jianshu.com/p/bb1b72a1623e