由于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名时，如果有成绩并列的情况不会将某些学生落下
具体哪一种方法可视应用场景而定
