MySQL中row_number() over排序函数功能实现

由于row_number() over 是Oracle中的函数，MySQL如何实现相同功能？

示例：想要取出每个课程前3名的学生信息、课程id，成绩与对应课程内排名

创建student表：
s_id为学生id，s_name为学生姓名，s_sex为性别
创建score表：
s_id 为学生id，c_id为课程id，s_score为对应的成绩

create table student (s_id int,
                      s_name varchar(45),
                      s_sex varchar(25));
insert into student values
(01,'赵一','男'),
(02,'钱二','男'),
(03,'孙三','男'),
(04,'李四','男'),
(05,'周五','女'),
(06,'吴六','女'),
(07,'郑七','女'),
(08,'王八','女');

create table score (s_id int,
                    c_id int,
                    s_score int);                   
insert into score values
(01,01,80),
(01,02,90),
(01,03,99),
(02,01,70),
(02,02,60),
(02,03,80),
(03,01,80),
(03,02,80),
(03,03,80),
(04,01,50),
(04,02,30),
(04,03,20),
(05,01,76),
(05,02,87),
(06,01,31),
(06,03,34),
(07,02,89),
(07,03,98)；

student

score

#内嵌部分：
set @rank:=0;
select *, @rank:=case when @current_id<>c_id then 1 else @rank+1 end as rank,
       @current_id:=c_id
from score 
order by c_id, s_score desc;

注意：@current_id=c_id，当c_id不是当前的课程时，rank重新从1开始计数，否则在当前rank上加1，@current_id赋值次序不能错，第一个正好未赋值，case when @current_id<>c_id 成立then 1执行
此时的排序需要现基于课程id，再基于成绩逆序

内嵌部分执行结果

错误示范：

#将 @current_id:=c_id提到前面，此时不能得到想要的结果，因为经过赋值 @current_id:=c_id始终成立
set @rank:=0;
select *, @current_id:=c_id,  @rank:=case when @current_id<>c_id then 1 else @rank+1 
                                     end as rank
from score 
order by c_id, s_score desc;

错误示范

#整体连起来写：
set @rank:=0;
select a.*, b.c_id, b.s_score, b.rank 
from(select *, @rank:=case when @current_id<>c_id then 1 else @rank+1 end as rank, @current_id:=c_id 
     from score 
     order by c_id,s_score desc)b
left join student a 
on a.s_id=b.s_id
having rank<=3
order by c_id, rank

整体最终执行结果

注意：在最后的条件设定中需要用having不能用where，因为在原表中是不存在rank字段的，这是我们为了取数所构造的

参考学习：https://www.jianshu.com/p/3b6f687809e5
感谢作者分享！
————————————————————————————————————————
方法2：

#步骤1：计数形成rnk列
create table x as
select a.s_id, a.c_id, a.s_score,
       (select count(*) from score b where a.c_id=b.c_id and a.s_score<b.s_score) as rnk
from score a
order by a.c_id,(select count(*) from score b where a.c_id=b.c_id and a.s_score<b.s_score)
#步骤2：连接两表，取前三
select x.s_id, y.s_name, x.c_id, x.s_score, rnk+1 as rnk
from x
left join student y
on x.s_id = y.s_id
where rnk=0 or rnk=1 or rnk=2
order by x.c_id, rnk

步骤一执行结果

最终结果

此方法在实际应用于抽取成绩前3名时，如果有成绩并列的情况不会将某些学生落下
具体哪一种方法可视应用场景而定