MySQL排名函数实现
来源:SegmentFault
时间:2023-01-20 08:06:44 251浏览 收藏
对于一个数据库开发者来说,牢固扎实的基础是十分重要的,golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《MySQL排名函数实现》,主要介绍了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以下的版本有什么方法实现呢,使用用户变量,记录名次。
用户变量:以"
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 s_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 |
参考文档
https://cloud.tencent.com/developer/article/1562954
https://www.jianshu.com/p/bb1b72a1623e
文中关于mysql的知识介绍,希望对你的学习有所帮助!若是受益匪浅,那就动动鼠标收藏这篇《MySQL排名函数实现》文章吧,也可关注golang学习网公众号了解相关技术文章。
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
-
259 收藏
-
411 收藏
-
476 收藏
-
312 收藏
-
244 收藏
-
195 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习
-
- 精明的狗
- 很详细,码住,感谢作者大大的这篇博文,我会继续支持!
- 2023-03-17 08:27:40
-
- 成就的帆布鞋
- 这篇文章出现的刚刚好,太详细了,很棒,已加入收藏夹了,关注作者大大了!希望作者大大能多写数据库相关的文章。
- 2023-03-08 13:27:04
-
- 舒服的裙子
- 这篇博文真是及时雨啊,很详细,写的不错,mark,关注师傅了!希望师傅能多写数据库相关的文章。
- 2023-02-25 02:04:02
-
- 舒服的裙子
- 很有用,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,看完之后很有帮助,总算是懂了,感谢师傅分享文章!
- 2023-02-18 11:30:15
-
- 潇洒的酒窝
- 这篇技术文章太及时了,好细啊,赞 👍👍,已收藏,关注大佬了!希望大佬能多写数据库相关的文章。
- 2023-02-17 14:50:54