登录
首页 >  数据库 >  MySQL

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

执行结果

ab
12

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

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

SELECT name,score, @rank:=@rank+1 `rank` from s_score s,(select @rank:=0) q ORDER BY score desc
namescorerank
赵六961
王五962
大卫913
小明904
小龙885
梁晨876
小五807
张三808
赵武809
李四7010
威廉6911
小绿6912
小红6013

并排名次展示

现在还有一个问题,出现分数相同,并列排名,名次应该相同。我们使用一个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
namescorerank
赵六961
王五961
大卫912
小明903
小龙884
梁晨875
小五806
张三806
赵武806
李四707
威廉698
小绿698
小红609

并排名次跳过

如果出现并列排名,下一个名次将自动跳过,比如出现两个并列第一,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
namescorerank
赵六961
王五961
大卫913
小明904
小龙885
梁晨876
小五807
张三807
赵武807
李四7010
威廉6911
小绿6911
小红6013

使用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
    
namescorerankrowdense
赵六96111
王五96121
大卫91332
小明90443
小龙88554
梁晨87665
赵武80776
小五80786
张三80796
李四7010107
小绿6911118
威廉6911128
小红6013139

参考文档

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

文中关于mysql的知识介绍,希望对你的学习有所帮助!若是受益匪浅,那就动动鼠标收藏这篇《MySQL排名函数实现》文章吧,也可关注golang学习网公众号了解相关技术文章。

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表