【MYSQL】实现排名函数RANK，DENSE_RANK和ROW_NUMBER

最新推荐文章于 2024-06-21 18:07:31 发布
最新推荐文章于 2024-06-21 18:07:31 发布
阅读量830 收藏 1
点赞数
分类专栏： 【数据分析】Sql学习
原文链接：https://blog.csdn.net/u011726005/article/details/94592866
版权

转载出处：https://blog.csdn.net/u011726005/article/details/94592866

 

目录         

         1. 排名分类

         1.1 区别RANK，DENSE_RANK和ROW_NUMBER

1.2 分组排名

2. 准备数据

3. 不分组排名

         3.1 连续排名ROW_NUMBER

         3.2 并列跳跃排名RANK

3.3 并列连续排名DENSE_RANK

4. 分组排名

4.1 分组连续排名ROW_NUMBER

4.2 分组并列跳跃排名RANK

4.3 分组并列连续排名DENSE_RANK

窗口函数不同于常规的聚合函数， 使用的窗口函数不会导致行被分组到一个单一的输出行；行保留其独立的身份。 在后台，窗口函数能够访问的不止查询结果的当前行。

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

1. 排名分类


1.1 区别RANK，DENSE_RANK和ROW_NUMBER

 

  1. RANK并列跳跃排名：并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
  2. DENSE_RANK并列连续排序：并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
  3. ROW_NUMBER连续排名：即使相同的值，依旧按照连续数字进行排名。


区别如图：


1.2 分组排名


将数据分组后排名，区别如图：


2. 准备数据


创建一张分数表，里面有字段：分数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. 不分组排名


3.1 连续排名ROW_NUMBER

 

  • 使用 ROW_NUMBER 实现：
SELECT 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as rank
FROM 
    score;

 

  • 使用 变量 实现：
SELECT 
    s.score, 
    (@cur_rank := @cur_rank + 1) ranking
FROM 
    score s, 
    (SELECT @cur_rank := 0) r
ORDER BY 
    score DESC;



结果如图：


3.2 并列跳跃排名RANK

 

  • 使用 RANK 实现：
SELECT 
    course_id, 
    score,
    RANK() OVER(ORDER BY score DESC)
FROM 
    score;
  • 使用 变量和IF语句 实现：

# rank_counter:排名自增计数，一直往上加
# cur_rank:表示当前排名
# pre_score:表示前一个分数
# 每前进一条记录，rank_counter就加一
# 如果前一个分数等于当前分数，那就保持当前排名,如果不等于，直接跳到rank_counter的位置
# 要继续前进时，pre_score置为当前分数

SELECT  
    s.score,
    @rank_counter := @rank_counter + 1,
    IF(@pre_score = s.score, @cur_rank, @cur_rank := @rank_counter) ranking,
    @pre_score := s.score
FROM 
    score s, 
    (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r
ORDER BY 
    s.score DESC;
  • 使用 变量和CASE语句 实现：
SELECT 
    s.score,
    @rank_counter := @rank_counter + 1,
    (
        CASE
        WHEN @pre_score = s.score THEN @cur_rank
        WHEN @pre_score := s.score THEN @cur_rank := @rank_counter
        END
    ) ranking
FROM 
    score s, 
    (SELECT @cur_rank :=0, @pre_score := NULL, @rank_counter := 0) r # 初始化参数
ORDER BY 
    s.score DESC;



结果如图：

 

3.3 并列连续排名DENSE_RANK

 

  • 使用 DENSE_RANK 实现：
SELECT 
    course_id, 
    score,
    DENSE_RANK() OVER(ORDER BY score DESC) 
FROM 
    score;

 

  • 使用 变量和IF语句 实现：

# cur_rank:表示当前排名
# pre_score:表示前一个分数
# 如果前一个分数等于当前分数，那就保持当前排名,如果不等于，+1
# 要继续前进时，pre_score置为当前分数

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

 

  • 使用 变量和CASE语句 实现：
SELECT 
    s.score,
    (
        CASE
        WHEN @pre_score = s.score THEN @cur_rank
        WHEN @pre_score := s.score THEN @cur_rank := @cur_rank + 1
        END
    ) ranking
FROM 
    score s, 
    (SELECT @cur_rank :=0, @pre_score = NULL) r
ORDER BY 
    s.score DESC;



结果如图：


4. 分组排名

PARTITION BY course_id ORDER BY score DESC)  其实就像普通order by course_id,score


4.1 分组连续排名ROW_NUMBER

 

  • 使用 ROW_NUMBER 实现：
SELECT 
    course_id, 
    score,
    ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) ranking 
FROM 
    score;

 

# cur_rank:表示当前排名
# pre_course_id:表示前一个课程ID
# 如果前一个课程ID等于当前课程ID（说明还是一门课的），+1，否则就将cur_rank置为1
# 要继续前进时，pre_score置为当前分数

  • 使用 变量和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 
    course_id, 
    score DESC;



结果如图：

 


4.2 分组并列跳跃排名RANK

 

  • 使用 RANK 实现：
SELECT 
    course_id, 
    score,
    RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM 
    score;

 

  • 使用 变量和IF语句 实现：

# rank_counter:排名自增计数，一直往上加（不过此时要看前后记录是否是同一课程）
# cur_rank:表示当前排名

# pre_course_id:表示前一个课程号
# pre_score:表示前一个分数


# 每前进一条记录，如果前后是同一课程，rank_counter就加一，否则就重置为1    这是temp1
# 如果前后是同一课程，而且前后分数相同，那cur_rank不变 ,如果前后分数不同，cur_rank直接跳到rank_counter的位置，如果前后不是同一课程，cur_rank置为1
# 要继续前进时，pre_score置为当前分数，pre_score_id置为当前课程id

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 分组并列连续排名DENSE_RANK

 

  • 使用 DENSE_RANK 实现：
SELECT 
    course_id, 
    score,
    DENSE_RANK() OVER(PARTITION BY course_id ORDER BY score DESC)
FROM 
    score;

 

  • 使用 变量和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;



结果如图：

YYIverson
关注
专栏目录
ROW_NUMBER、DENSE_RANK和RANK函数
weixin_42447367的博客
08-21 888
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。一般情况下row_number()函数和over连用 1）ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的） with q1 as (select 1 a,1 b from dual union select 1,2 from dual un
MYSQL实现排名函数RANK，DENSE_RANK和ROW_NUMBER
qq_25933249的博客
02-08 412
1. 排名分类 1.1 区别RANK，DENSE_RANK和ROW_NUMBER RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。 DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。 ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。 区别如图： 1.2 分组排名 将数据分组后排名，区别如图： 2. 准备数据 创建一张分数表，里面有字段：分数score，课程号course
MYSQL 使用及实现排名函数RANK、DENSE_RANK和ROW_NUMBER
hayhead的博客
04-01 1186
</svg> <hr> 文章目录 1. 排名分类1.1 区别RANK，DENSE_RANK和ROW_NUMBER1.2 分组排名 2. 准备数据3. 不分组排名3.1 连续排名3.2 并列跳跃排名3.3 并列连续排名 4. 分组排名4.1 分组连续排名4.2 分组并列跳跃排名4.3 分组并列连续排名...
MySQL：MySQL分组排序函数rank()、row_number()、dense_rank()与partition by结合使用
最新发布
永远保持随时离开的技术能力！
06-21 685
MySQL分组排序函数rank()、row_number()、dense_rank()与partition by结合使用
MySQL查询排名的2种方式：变量排名、窗口函数
小龙在线
03-31 4483
例如，你可能希望按照班级对学生的分数进行排名。有时候，你可能想要查看排名的某个范围，例如，查看排名在前10名的学生。请注意，这些示例都是基于一个假设的students表的。根据你的实际数据表结构和需求，你可能需要对这些示例进行调整。这些示例都是基于一个假设的students表的。在这个查询中，RANK()函数首先将学生按照班级进行分组，然后在每个班级内按照分数进行排名。在这个查询中，我们首先计算所有学生的排名，然后通过WHERE子句筛选出特定学生的排名。如果你想要查询某个特定学生的排名，可以使用子查询。
MySQL中多种排名实现
程序员Forlan的博客
02-01 4821
不同版本MySQL排名实现，利用排名函数，利用自定义变量，不重复连续、并列连续、并列不连续
MySQL排名、并列排名
SmartTxp的博客
11-21 727
一、使用@自定义变量 1、MySQL的@与@@区别 @x 是 用户自定义的变量 (User variables are written as @var_name) @@x 是 global或session变量 (@@global @@session ) 2、@@查看全局变量: select @@log_error; 3、...
rank()函数 mysql_在MySQL中实现Rank高级排名函数
weixin_34481252的博客
01-30 828
在MySQL中实现Rank高级排名函数MySQL中没有Rank排名函数，当我们需要查询排名时，只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此，可不要小瞧基础而简单的查询语句，我们可以利用其来达到Rank函数一样的高级排名效果。在这里我用一个简单例子来实现排名的查询：首先我们先创建一个我们需要进行高级排名查询的players表，CREATE TABLE `players` (`pi...
mysql rank函数_Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
weixin_35777914的博客
01-19 3086
排名函数是Sql Server2005新增的功能，下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。表结构和初始数据Sql附上表结构和初始数据图：一、ROW_NUMBERrow_number的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必...
SQL 开窗函数，row_number, dense_rank, percent_rank
weixin_43862728的博客
04-19 1618
row_number dense_rank percent_rank
ORACLE转MYSQL各种函数说明（ROW_NUMBER() ，COUNT()，DENSE_RANK()）
qq_15845499的博客
04-11 2231
ORACLE转MYSQL各种函数说明 涉及函数： ROW_NUMBER() OVER(order by x desc) ROW_NUMBER() OVER(PARTITION BY x ORDER BY y) COUNT() OVER(PARTITION BY x order by y desc) DENSE_RANK() OVER(ORDER BY x) 1、通用公式：（分组排序加序号） O...
低版本mysql 实现row_number() ,rank() ,dense_rank()及其窗口函数
A_single_cat的博客
11-24 1942
测试表 create table test (id int(4) , name VARCHAR(10) ) row_number() 实现 SELECT t.name , t.id, (@cur_rank := @cur_rank + 1) ranking FROM test t , (SELECT @cur_rank := 0) r ORDER BY id DESC; ROW_NUMBER() over(partiton by name ORDER BY id DESC) 实现
oracle 和 mysql 排序 rank（），dense_rank()
~O~
01-16 286
mysql 的rank可以参考这篇博文https://blog.csdn.net/justry_deng/article/details/80597916 oracle 的rank()，dense_rank() 这2个函数使用方式基本一致，下面举例说明： 表test_user数据如下： rank() 非连续排序，如果有分数相同的，下一个排序等级会跳过对应的数量，如下...
mysql上排名sql的写法，类似oracle的rank和dense
S_Istvan的博客
03-20 662
转：http://mxohy.blog.sohu.com/172181390.html?qq-pf-to=pcqq.c2c 这几天开发提交了几个排名的sql，oracle环境下这类问题就很好解决了，row_number()，rank()或者dense()函数就能搞定，但mysql环境下没有这类函数，那就自己搞： 测试如下： mysql> select * from animals_inno;
关于函数row_number，rank,dense_rank排序
一花一草一世界
09-10 763
eg:返回排名前三的用户 select no,           sal,           row_number() over(partition by mo order by sal desc) as row_number,           rank() over(partition by mo order by sal desc) as rank,           de
排序函数row_number、rank、dense_rank的区别【sql boy】
佛系研究生8的博客
07-06 474
排序函数row_number、rank、dense_rank的区别 口诀： row_number 不存在并列 dense_rank 和rank存在并列，但rank很跳。 口诀的意思是： row_number：不考虑并列的情况，哪怕分数相同，排名都是一溜下来的自然数。 dense_rank和rank 考虑并列的情况，区别在于rank很跳，并列排名的个数会影响接下来的排名，表现为数字的中断。而dense_rank 不管有几个并列的第5名，接下来都是从6开始排。 ...
MySQL 排名函数 RANK, DENSE_RANK, ROW_NUMBER
安全新司机
10-22 1356
DENSE_RANK(): 并列连续排序, 并列即相同的值, 相同的值保留重复名次, 遇到下一个不同值时, 依然按照连续数字排名。RANK(): 并列跳跃排名, 并列即相同的值, 相同的值保留重复名次, 遇到下一个不同值时, 跳跃到总共的排名。思路: 按照部门统计, 前三高薪水的员工信息, 需要用到排名函数, 需要保持排名的连续性, 结合。RANK, DENSE_RANK, ROW_NUMBER 函数在查询结果上的区别。ROW_NUMBER(): 连续排名, 即使相同的值, 依旧按照连续数字进行排名。
求数据项的名次和某项在整个数据中名次
weixin_30512043的博客
03-03 71
1 求数据项的名次 SET @counter=0; SELECT @counter:=@counter+1 as rank, s.id,s.sname,e.item_1+e.item_2+e.item_3+e.item_4+e.item_5+e.item_6+e.item_7+e.item_8+e.item_9+e.item_10+e.item_11+e.item_12+e.item_13...
row number函数_Hive排名函数ROW_NUMBER,RANK 和 DENSE_RANK的区别
weixin_39914049的博客
12-15 166
需求描述：分析最近三个月每天排名前30的邮箱收件数量。涉及到的表字段：发件时间，收件人。一、ROW_NUMBER表t查询出了3月每天不同类型邮箱收件量的降序排名，表t1通过ROW_NUMBER函数给每行数据添加序列号，select ROW_NUMBER() over(partition by t.send_dt order by t.num desc) as row_num，在表t1的基础上查询r...
mysql rank row_number
08-24
在MySQL中，可以使用RANK()、DENSE_RANK()和ROW_NUMBER()函数来查询每个用户订单金额最高的订单信息。这些函数的主要区别如下： - RANK()函数：根据指定的排序顺序对于每个用户的订单金额进行排名，如果存在相同的订单金额，会跳过相同的排名并留下空位。例如，如果有两个订单金额最高的订单，它们的排名就会是1和2，而不是两个都是1。 - DENSE_RANK()函数：与RANK()函数类似，它也根据指定的排序顺序对于每个用户的订单金额进行排名。但是，与RANK()函数不同的是，它不会跳过相同的排名，而是会按照相同的排名依次递增。例如，如果有两个订单金额最高的订单，它们的排名就会是1和1，而不是1和2。 - ROW_NUMBER()函数：根据指定的排序顺序为每个用户的订单金额分配唯一的行号。它不会考虑相同的订单金额，而是简单地为每个订单按照顺序分配行号。例如，如果有两个订单金额最高的订单，它们的行号就会是1和2。 你可以使用以下SQL查询来获取每个用户订单金额最高的订单信息： <span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* *3* [[Mysql] RANK()函数 | ROW_NUMBER()函数 | DENSE_RANK()函数](https://blog.csdn.net/Hudas/article/details/124584662)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值