mysql使用自定义序列实现开窗函数row_number功能(步骤详解)_自定义row_number 函数

业务场景

在做east5.0的时候，想要实现hive库迁移mysql库，此时絮叨一个难搞的问题，就是 row_number() over(partition by ... order by...)这种开窗函数分组排序的mysql在8.0之后才支持，而我用的是5.+不支持（此处心里咒骂David、Monty Widenius N+1遍）。
在一通百度之后终于知道该怎么在 mysql 里面实现 row_number() 排序了。
话不多说 show time:

实现

一、建表

create table grades(
`name` varchar(10) comment '姓名',
`subject` varchar(10) comment '科目',
`score` int(10) comment '分数',
primary key(name,subject))
``
## 二、数据
```sql
insert into grades(name, subject, score)
values('小明', '语文', 85),
('小华', '语文', 89),
('小李', '语文', 91),
('小芳', '语文', 93),
('小明', '数学', 77),
('小华', '数学', 95),
('小李', '数学', 83),
('小芳', '数学', 88),
('小明', '英语', 90),
('小华', '英语', 92),
('小李', '英语', 85),
('小芳', '英语', 88)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

按照每个科目分组排序

实现1：

SELECT IF(@subject_pre=t1.subject ,@rn:=@rn+1 ,@rn :=1 ) AS rn
     , t1.*
     , (@subject_pre:=SUBJECT) 
FROM grades t1, 
(SELECT @rn:=0, @subject_pre:='') AS t2
ORDER BY SUBJECT, score DESC;
1
2
3
4
5
6

实现2：

select (@i:=case when @subject_pre=t1.subject then @i+1 else 1 end) as rn
     , t1.*
     , (@subject_pre:=subject) 
from grades t1, 
(select @i:=0, @subject_pre:='') as t2
order by subject, score desc

1
2
3
4
5
6
7

查询结果展现：

解释一下：
添加一个比较项 subject_pre， 记录前一个科目是什么。
再加上一个自增的序列，实现index+1的功能。
因为数据已经是有序的，如果指向的科目和存储的前一个科目相同，那么序号+1，否则的话，序号从1开始重新计算。
这样就实现了分组排序。

题外

这么一看mysql其实还是挺有意思的，这种写法感觉有点偏于编程语言的感觉了。
at last:
想建一个数据库技术的交流群，用于磨炼提升技术能力.群号： 130730832 ,欢迎大佬前来教学。