低版本mysql 利用@变量实现row_number() over(partition by order by )排序功能

最新推荐文章于 2023-05-19 13:31:48 发布

原创最新推荐文章于 2023-05-19 13:31:48 发布

· 2.0k 阅读

10 ·

版权

MYSQL 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了在MySQL 8.0之前如何利用@变量模拟窗口函数row_number()、rank()和dense_rank()实现排名功能。详细展示了通过变量进行简单排序、分组排序的实例，并给出了测试数据和结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL实现排名两种方式

一、窗口函数
MySQL 8.0版本用窗口函数就可以实现排名，有三种方式，对相同值的处理不同：

row_number()：不产生相同的记录，没有序号间隔（值一致，排名不一致）
rank()：产生相同的记录，有序号间隔（值一致，排名一致，名次占位）
dense_rank()：产生相同记录，没有序号间隔（值一致，排名一致，名次不占位）
（由于未安装8.0版本mysql，下面用oracle进行测试讲解）

（一）准备测试数据

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select * from scores_tab

表数据如下：
在这里插入图片描述
（二）测试讲解
1、row_number()：
1）简单排序

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select a.*,row_number() over(order by score desc ) as rn
from scores_tab a order by rn

在这里插入图片描述
2）分组排序

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select a.*,row_number() over(partition by class_name order by score desc ) as rn
from scores_tab a order by class_name,rn

跑数结果：
在这里插入图片描述
2、rank()：
不分组排序：

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select a.*,rank() over(order by score desc ) as rn
from scores_tab a order by rn

跑数结果：
在这里插入图片描述
分组排序：

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select a.*,rank() over(partition by class_name order by score desc ) as rn
from scores_tab a order by class_name,rn

跑数结果：
在这里插入图片描述
3、dense_rank()：

with scores_tab as (
select '1班' as class_name,'01' as student_id ,100 as score from dual union all
select '1班' as class_name,'02' as student_id ,90  as score from dual union all
select '1班' as class_name,'03' as student_id ,83  as score from dual union all
select '1班' as class_name,'04' as student_id ,77  as score from dual union all
select '1班' as class_name,'05' as student_id ,77  as score from dual union all
select '1班' as class_name,'06' as student_id ,60  as score from dual union all
select '2班' as class_name,'01' as student_id ,97  as score from dual union all
select '2班' as class_name,'02' as student_id ,88  as score from dual union all
select '2班' as class_name,'03' as student_id ,60  as score from dual union all
select '2班' as class_name,'04' as student_id ,54  as score from dual union all
select '2班' as class_name,'05' as student_id ,33  as score from dual  )

select a.*,dense_rank() over(partition by class_name order by score desc ) as rn
from scores_tab a order by class_name,rn

在这里插入图片描述

二、变量
低版本MySQL通过变量实现排序功能。
（一）准备测试数据：

drop table if exists fxm_test_table_scores;
CREATE TABLE IF NOT EXISTS `fxm_test_table_scores` (
  `class_name` varchar(4) COLLATE utf8_bin NOT NULL COMMENT '班级',
  `student_id` varchar(4) COLLATE utf8_bin NOT NULL COMMENT '学号',
  `score` DECIMAL(10,2) COLLATE utf8_bin NOT NULL COMMENT '成绩',
  PRIMARY KEY (`class_name`,`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='测试成绩表';commit;

insert into fxm_test_table_scores (class_name,student_id,score)values
('1班','01',100),
('1班','02',90),
('1班','03',83),
('1班','04',77),
('1班','05',77),
('1班','06',60),
('2班','01',97),
('2班','02',88),
('2班','03',60),
('2班','04',54),
('2班','05',33);commit;

表数据如下：
成绩表数据

（二）脚本测试：
1、简单排名：1,2,3,4…

select 
    class_name,student_id,score,rank
FROM
    (
    select  a.*,@r:=@r+1 AS rank
    FROM fxm_test_table_scores a,(select @r:=0)r
    ORDER BY a.score desc  
    )t1

查询结果：

在这里插入图片描述

2、分组排名：1,2,3,4…

select 
    class_name,student_id,score,rank
FROM
    (
    select  a.*,IF(@p=a.class_name,@r:=@r+1,@r:=1) AS rank,
    @p:= a.class_name
    FROM fxm_test_table_scores a,(select @p:=NULL,@r:=0)r
    ORDER BY a.class_name,a.score desc  
    )t1

查询结果：
在这里插入图片描述

3、分组排名（并列排名不占位）：1,2,2,3…

SELECT 
     class_name,student_id,score,rank
FROM
    (SELECT a.*,
            IF(@p=class_name,CASE WHEN @s=score THEN @r WHEN @s:=score THEN @r:=@r+1 END,@r:=1 ) AS rank,
            @p:=class_name,
            @s:=score
    FROM fxm_test_table_scores a,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
    ORDER BY class_name,score desc
    ) x

查询结果：
在这里插入图片描述

4、分组排名（并列排名占位）：1,2,2,4…

SELECT 
    class_name,student_id,score,rank
FROM 
(select a.*
,case when class_name = @last_class then @group_count:=@group_count+1 else @group_count:=1 end as group_count
,case when class_name = @last_class then if(score=@last_score,@rank:=@rank,@rank:=@group_count)  else @rank:=1 end as rank
,@last_class:=class_name
,@last_score:=score
from fxm_test_table_scores a, 
(select @last_class:= '' , @rank:=0 ,@group_count=1,@last_score:=0) T  
order by class_name,score DESC
) x