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

最新推荐文章于 2024-06-01 13:03:59 发布

 shammy

最新推荐文章于 2024-06-01 13:03:59 发布

阅读量1.9k

点赞数 3

分类专栏： MYSQL

本文链接：https://blog.csdn.net/shammy_feng/article/details/112308170

版权

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