oracle 查询各科前3名_ORACLE分科目统计每科前三名的学生的语句

成绩表 score(student_no,Subject_no,Score)分别为学号，课程号，成绩

查询出每科的前三名学生的学号

创建表并插入测试数据

create table score(student_no varchar2(3),Subject_no varchar2(20),Score number);insert into score values('001','语文',70);insert into score values('001','数学',60);insert into score values('001','英语',90);insert into score values('002','语文',78);insert into score values('002','数学',67);insert into score values('002','英语',80);insert into score values('003','语文',89);insert into score values('003','数学',60);insert into score values('003','英语',97);insert into score values('004','语文',50);insert into score values('004','数学',67);insert into score values('004','英语',70);insert into score values('005','语文',79);insert into score values('005','数学',65);insert into score values('005','英语',79);insert into score values('006','语文',78);insert into score values('006','数学',56);insert into score values('006','英语',87);commit;

第一种查询:

查询的sql语句:

select *from (select s.*,row_number() over(partition by s.subject_no order by s.score desc) pfrom score s)
where p <= 3

结果如下

第二种查询:

查询的sql语句

select *from (select s.*,rank() over(partition by s.subject_no order by s.score desc) pfrom score s)where p <= 3

结果如下

区别

row_number:不管排名是否有相同的，都按照顺序1，2，3…..n
rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次
dense_rank:排名相同的名次一样，且后面名次不跳跃