由于row_number() over 是Oracle中的函數(shù)辣苏，MySQL如何實現(xiàn)相同功能包归？
示例：想要取出每個課程前3名的學(xué)生信息传蹈、課程id距境，成績與對應(yīng)課程內(nèi)排名
創(chuàng)建student表：
s_id為學(xué)生id，s_name為學(xué)生姓名祭阀，s_sex為性別
創(chuàng)建score表：
s_id 為學(xué)生id鹉戚，c_id為課程id，s_score為對應(yīng)的成績
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
#內(nèi)嵌部分：
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抹凳，當(dāng)c_id不是當(dāng)前的課程時，rank重新從1開始計數(shù)伦腐，否則在當(dāng)前rank上加1赢底，@current_id賦值次序不能錯，第一個正好未賦值柏蘑，case when @current_id<>c_id 成立then 1執(zhí)行
此時的排序需要現(xiàn)基于課程id幸冻，再基于成績逆序
內(nèi)嵌部分執(zhí)行結(jié)果
錯誤示范：
#將 @current_id:=c_id提到前面，此時不能得到想要的結(jié)果咳焚，因為經(jīng)過賦值 @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
整體最終執(zhí)行結(jié)果
注意：在最后的條件設(shè)定中需要用having不能用where，因為在原表中是不存在rank字段的革半，這是我們?yōu)榱巳?shù)所構(gòu)造的
參考學(xué)習(xí)：http://www.reibang.com/p/3b6f687809e5
感謝作者分享碑定！
————————————————————————————————————————
方法2：
#步驟1：計數(shù)形成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
步驟一執(zhí)行結(jié)果
最終結(jié)果
此方法在實際應(yīng)用于抽取成績前3名時，如果有成績并列的情況不會將某些學(xué)生落下
具體哪一種方法可視應(yīng)用場景而定
