本文出处 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
执行后果
a | b |
---|---|
1 | 2 |
:= 是赋值的意思,与编程语言赋值有点区别。上面开始展现应用简略 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 (<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
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 |
以上就是排序名次全副实现形式了,还有其余实现形式,麻烦在评论里补充一下。
参考文档
https://cloud.tencent.com/developer/article/1562954
https://www.jianshu.com/p/bb1b72a1623e