mysql over 排序_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

from score a

order by a.c_id,(select count(*) from score b where a.c_id=b.c_id and a.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名时，如果有成绩并列的情况不会将某些学生落下

具体哪一种方法可视应用场景而定