MySQL 实现排名（分组排名）

最新推荐文章于 2024-08-15 15:59:36 发布
最新推荐文章于 2024-08-15 15:59:36 发布
阅读量6.8k 收藏 26
点赞数 6
分类专栏： Mysql 文章标签： mysql 分组排名
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43563538/article/details/119565150
版权

在MYSQL的最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在5.*版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句（CASE WHEN或IF）和添加临时变量。

基本知识：

sql语句中，使用@来定义一个变量。如：@abc

sql语句中，使用:=来给变量赋值,：@abc:=123,则变量abc的值为123

sql语句中，if(A,B,C)表示，如果A条件成立，那么执行B，否则执行C，如:@abc := if(2>1,100,200)的结果是，abc的值为100。

一、排名分类：
1.1 区别RANK，DENSE_RANK和ROW_NUMBER

  • RANK并列跳跃排名：，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。

  • DENSE_RANK并列连续排序：，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。

  • ROW_NUMBER连续排名：，即使相同的值，依旧按照连续数字进行排名。

二、数据准备：

创建一张分数表，里面有字段：分数score，课程号course_id和学生号student_id。执行如下SQL语句，进行导入数据。

create table score(
   student_id varchar(10),
   course_id varchar(10),
   score decimal(18,1)
);
​
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
insert into score values('08' , '02' , 89);
insert into score values('09' , '02' , 89);
三、不分组排名
3.1 连续排名：
  1. 使用ROW_NUMBER实现：
>SELECT score,
ROW_NUMBER() OVER (ORDER BY score DESC) ranking
FROM score;
  1. 使用变量实现：
SELECT 
 s.score,(@cur_rank := @cur_rank + 1) AS Rank
FROM score s,(SELECT @cur_rank := 0) r
ORDER BY s.score DESC
3.2 并列跳跃排名
  1. 使用RANK实现：
SELECT course_id, score,
 RANK() OVER(ORDER BY score DESC)
FROM score;
  1. 使用变量和IF语句实现：
SELECT s.score ,
 @cur_count := @cur_count + 1,
 if(@pre_score = s.score,@cur_rank,@cur_rank := @cur_count) ranking,
 @pre_score := s.score
FROM score s,(SELECT @cur_count := 0,@cur_rank:=0,@pre_score := NULL) t
ORDER BY s.score DESC
Result1
  1. 使用变量和CASE来实现：
SELECT s.score,
@cur_count := @cur_count + 1,
(
 case 
 when @pre_score = s.score then @cur_rank
 when @pre_score := s.score then @cur_rank := @cur_count 
 END 
) ranking,
@pre_score := s.score
FROM score s,(SELECT @cur_count := 0,@cur_rank := 0,@pre_score := NULL) r
ORDER BY s.score DESC ;
3.3 并列连续排名
  1. 使用DENSE_RANK实现：
SELECT course_id, score,
DENSE_RANK() OVER(ORDER BY score DESC) FROM score;
  1. 使用变量和IF语句实现：
SELECT 
 s.score,
 @cur_rank := @cur_rank + 1 ranking
FROM SCORE s,(SELECT @pre_score := NULL ,@cur_rank := 0)  r 
ORDER BY s.score DESC ;
  1. 使用变量和CASE语句实现：
SELECT 
 s.score,
 case 
 when @pre_score = score then @cur_rank
 when @pre_score := score then @cur_rank := @cur_rank + 1 END ranking
FROM SCORE s,(SELECT @pre_score := NULL ,@cur_rank := 0)  r 
ORDER BY s.score DESC ;
四、分组排名
4.1 分组连续排名
  1. 使用ROW_NUMBER实现：
SELECT 
 course_id, 
 score,
 ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking 
FROM score;
  1. 使用变量和IF实现：
SELECT 
 s.course_id,
 s.score,
 if(@pre_course_id = s.course_id,@cur_rank := @cur_rank + 1 ,@cur_rank := 1) ranking,
 @pre_course_id := s.course_id
FROM score s,(SELECT @cur_rank := 0 ,@pre_course_id := NULL) r
ORDER BY s.course_id,s.score DESC ;
  1. 使用变量和CASE实现：
SELECT 
 s.course_id,
 s.score,
 (
 case 
 when @pre_course_id = s.course_id then @cur_rank := @cur_rank + 1
 when @pre_course_id := s.course_id then @cur_rank := 1 end
 ) ranking
FROM score s ,(SELECT @cur_rank := 0 ,@pre_course_id := NULL) r
ORDER BY s.course_id,s.score DESC
Result
4.2 分组并列跳跃排名
  1. 使用RANK实现：
SELECT course_id, score,
 RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM score;
  1. 使用变量和IF语句实现：
SELECT s.course_id, s.score,
IF(@pre_course_id = s.course_id,
 @rank_counter := @rank_counter + 1,
 @rank_counter := 1) temp1,
IF(@pre_course_id = s.course_id,
 IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter),
 @cur_rank := 1) ranking,
@pre_score := s.score temp2,
@pre_course_id := s.course_id temp3
FROM score s, (SELECT @cur_rank := 0, @pre_course_id := NULL, @pre_score := NULL, @rank_counter := 1)r
ORDER BY s.course_id, s.score DESC;
4.3 分组并列连续排名
  1. 使用DENSE_RANK实现：
SELECT course_id, score,
DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM score;
  1. 使用变量和IF语句实现：
SELECT 
 s.course_id, 
 s.score,
 IF(@pre_course_id = s.course_id,
 IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1),
 @cur_rank := 1) ranking,
 @pre_score := s.score,
 @pre_course_id := s.course_id
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
ORDER BY course_id, score DESC;

可以将上述的IF条件提取出来：

SELECT 
 s.course_id, 
 s.score,
 IF(@pre_score = s.score, @cur_rank, @cur_rank := @cur_rank + 1) temp1,
 @pre_score := s.score temp2,
 IF(@pre_course_id = s.course_id, @cur_rank, @cur_rank := 1) ranking,
 @pre_course_id := s.course_id
FROM score s, (SELECT @cur_rank :=0, @pre_score = NULL, @pre_course_id := NULL) r
ORDER BY course_id, score DESC;

参考：https://blog.csdn.net/u011726005/article/details/94592866

゛Smlie。
关注
专栏目录
mysql 查询排名，包括并列排名和连续排名
m0_72642319的博客
06-24 1370
在MySQL中，根据不同的需求，查询排名可以分为并列排名和连续排名两种情况。
MySQL8 窗口函数 →_→ 专治GROUP BY后组内元素
weixin_73077810的博客
01-28 955
使用GROUP BY对数据进行分组后，让你计算组内元素的数值占整个组的比例，让你找组内前三大的元素等，你咋获取呀？
MySQL（四）：使用SQL实现排名
最新发布
欧阳方超的专栏
08-15 1216
RANK()与ROW_NUMBER()和DENSE_RANK()的区别在于，ROW_NUMBER()为每一行分配唯一的排名，而DENSE_RANK()则不会跳过排名。
MySQL实现排名、分组筛选、TopN问题
【逸辰杳】的博客
07-02 2052
通过对LeetCode第176,177,178,184,185题的归纳总结，梳理基于MySQL实现排名、分组精确筛选和分组范围筛选的实现方法
mysql 怎么实现组内排名_MySQL 实现排名（分组排名）
weixin_39770506的博客
01-18 1565
在MYSQL的最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在5.*版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句(CASE WHEN或IF)和添加临时变量。基本知识：sql语句中，使用@来定义一个变量。如：@abcsql语句中，使用:=来给变量赋值,：@abc:=123,则变量abc的值为123sql语句中，if(A,B,C...
MYSQL实现分组排名和不分组排名（函数RANK，DENSE_RANK和ROW_NUMBER）
liao3399084的博客
01-27 2038
创建一张分数表，里面有字段：分数score，课程号course_id和学生号student_id。执行如下SQL语句，进行导入数据。
mysql实现排名
weixin_44919041的博客
02-10 3099
1.mysql常用的排名函数 mysql在8.0提供了三个函数进行排名，RANK、DENSE_RANK和ROW_NUMBER函数，如果低于mysql8.0需要自己实现 2.RANK、DENSE_RANK和ROW_NUMBER使用 1.rank、dense_rank、row_number函数的使用 （1）创建表语句 CREATE TABLE `score_ranking` ( `rank_id` va...
mysql 计算排名
张默的博客
07-13 4705
  出每个人的排名 SELECT a1.Name, a1.Sales, COUNT(a2.Sales) Sales_Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales <= a2.Sales OR (a1.Sales=a2.Sales AND a1.Name = a2.Name) GROUP BY a1.Name, ...
mysql | 计算排名
```求知若渴，虚怀若谷```
07-12 352
https://blog.csdn.net/xxzhangx/article/details/78170019 >　计算排名 >　计算排名 给出每个人的排名 LeetCode上的计算排名： 方案一： 方案二： 方案三： 方案四： 例子： https://www.1keydata.c...
mysql分组排名
阿浪的专栏
03-04 1327
1.创建表并写入测试数据 create table tb_rank_score ( city varchar(20), score int ); insert into tb_rank_score values('sz',89); insert into tb_rank_score values('sz',76); insert into tb_rank_score values('sz',50); insert into tb_rank_score values('sz',89);
mysql实现分组排名
Regin的博客
05-13 1764
mysql实现分组排名 在oracle中，可以使用row_number() over(partition by ... order by ...)的方法实现分组排名，在mysql中并不提供类似方法，但也可以实现这样的功能 实例演示： 实例1：实现分组排名-单字段 # mysql实现分组排名-单字段 select t.member_id,# 分组字段 t.order_date,# 排名字段 # 排名计算逻辑 case when @num = t.member_id t
MySQL实现排名
12-14
0x01.需求 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。 +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）： +-------+---
MySQL实现排名/分组排名/合计（8.0窗口函数rank/dense_rank、5.6/5.7自行模拟）
reading-conference-writing
10-28 6706
介绍MySQL查询时直接显示排名/分组排名/分组合计的方法，支持5.6/5.7/8.0版本。涉及`rank()`和`dense_rank()`的使用、`window`窗口函数、MySQL变量的一些用法
mysql中的排名
Java领域优秀创作者
03-25 1617
本文详细介绍了在MySQL 5.7和MySQL 8.0中实现排名的方法。MySQL 5.7通过自连接和聚合函数实现排名，而MySQL 8.0通过窗口函数简化了这一过程。对于需要实现复杂排名的情况，MySQL 8.0的窗口函数提供了更强大的功能和更高的效率。MySQL 5.7需要复杂的自连接和聚合函数。SQL语句较长，易读性较差。性能相对较低。MySQL 8.0窗口函数简化了排名实现。SQL语句简洁，易读性好。性能优越，特别是对于大数据量的操作。
mysql 排名与排序
weixin_41762316的博客
05-05 1241
ROW_NUMBER() 为查询结果中的每一行分配一个唯一的数字 【就是每一行结果都是唯一的】RANK() 为查询结果中的每一行分配一个排名 【相同结果排名是一致的】RANK() 函数 条件再加上一个 emp_no 查询看看结果。后面跟 OVER（）括号里面跟查询条件，可以写多个条件 比如。分别是 ROW_NUMBER() 与 RANK()MySQL 8.0 版本新增 两个排名函数。所以一般排名就是要rank()函数。这里我用mysql自带的。
mysql如何进行分组排名
瓜瓜的笔记
01-04 1559
mysql8.0以上是支持窗口函数的,那么排名用窗口函数rank()就能轻松解决,但是大部分人还是用8.0一下的版本,一下代码可以结局如何进行分组排序. 先看一下我们的数据库的表,案例是随便乱填的,根据实际需求对号入座. 根据score按照userid进行分组排序. 代码如下 select userid,score, IF(@p=userid,@r:=@r+1,@r:=1) AS rank,(@p:=userid) as p from test , (SELECT @p:=NULL,@r:=1) a
MySQL面试题之分组排序并显示排名
Fanfan的博客
12-12 1364
题目 按各科成绩进行排序，并显示排名 !!!笛卡尔积链接 建表 -- 创建sc s_id学生编号，c_id科目编号，score成绩 create table sc( s_id varchar(10), c_id varchar(10), score int); -- 导入数据 insert into sc values ('01' , '01' , 80), ('01' , '02' , 9...
MySQL：MySQL分组排序函数rank()、row_number()、dense_rank()与partition by结合使用
永远保持随时离开的技术能力！
06-21 716
MySQL分组排序函数rank()、row_number()、dense_rank()与partition by结合使用
mysql语句如何写排名_MySQL排名SQL语句写法
weixin_30496435的博客
01-19 196
通常在使用MySQL时，会碰到根据多个排名条件计算一组数据的排名情况，条件相同的则排名一致。假设有表table，两个条件字段conditionA降序排序和conditionB升序排序，条件相同排名一致，排名信息保存在dense_rank字段。做法：定义@conditionA，@conditionB，@dense_rank, @id_rank四个用户变量。其中@conditionA，@cond...
mysql分组排名序号
04-20
好的，我了解了你的要求和问题。关于你的问题，可以使用以下 SQL 查询语句： SELECT (@RANK := @RANK + 1) AS rank, name, score FROM scores, (SELECT @RANK := 0) AS r GROUP BY id ORDER BY score DESC; 其中，scores 是包含成绩信息的表，包括学生的 id、name 和 score。 这个语句可以实现对分数的倒序排列，并在结果中添加排名序号。具体实现方式是使用 MySQL 的用户变量 @RANK，每读取一条记录，就自动加一，并将结果作为排名序号输出。
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

゛Smlie。

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值