mysql 排名_MySQL排名函数实现

最新推荐文章于 2023-07-18 11:04:19 发布

weixin_39534780

最新推荐文章于 2023-07-18 11:04:19 发布

阅读量263

点赞数

文章标签： mysql 排名 mysql将结果集并排显示

本文链接：https://blog.csdn.net/weixin_39534780/article/details/111278095

版权

作者：神易风

原文链接(底部链接可直达)：https://shenyifengtk.github.io/2020/07/22/MySQL%E6%8E%92%E5%90%8D%E5%87%BD%E6%95%B0%E5%AE%9E%E7%8E%B0/

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

数据库准备

创建一个分数表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

name	score	rank
赵六	96	1
王五	96	2
大卫	91	3
小明	90	4
小龙	88	5
梁晨	87	6
小五	80	7
张三	80	8
赵武	80	9
李四	70	10
威廉	69	11
小绿	69	12
小红	60	13

并排名次展示

现在还有一个问题，出现分数相同，并列排名，名次应该相同。我们使用一个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

name	score	rank
赵六	96	1
王五	96	1
大卫	91	2
小明	90	3
小龙	88	4
梁晨	87	5
小五	80	6
张三	80	6
赵武	80	6
李四	70	7
威廉	69	8
小绿	69	8
小红	60	9

并排名次跳过

如果出现并列排名，下一个名次将自动跳过，比如出现两个并列第一，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

name	score	rank
赵六	96	1
王五	96	1
大卫	91	3
小明	90	4
小龙	88	5
梁晨	87	6
小五	80	7
张三	80	7
赵武	80	7
李四	70	10
威廉	69	11
小绿	69	11
小红	60	13

使用SQL窗口函数

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

select 排序函数/聚合函数 over ( 分区字段 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

name	score	rank	row	dense
赵六	96	1	1	1
王五	96	1	2	1
大卫	91	3	3	2
小明	90	4	4	3
小龙	88	5	5	4
梁晨	87	6	6	5
赵武	80	7	7	6
小五	80	7	8	6
张三	80	7	9	6
李四	70	10	10	7
小绿	69	11	11	8
威廉	69	11	12	8
小红	60	13	13	9