分科目统计每科前三名的学生

一张表Tb_Student

insert into [Tb_Student] values('001','姜子牙','语文',80);
insert into [Tb_Student] values('001',姜子牙','数学',70);
insert into [Tb_Student] values('001','姜子牙','英语',90);

insert into [Tb_Student] values('002','娜吒','语文',88);
insert into [Tb_Student] values('002','娜吒','数学',77);
insert into [Tb_Student] values('002','娜吒','英语',85);

insert into [Tb_Student] values('003','雷震子','语文',99);
insert into [Tb_Student] values('003','雷震子','数学',67);
insert into [Tb_Student] values('003','雷震子','英语',94);

 生成结果

id    sname    Subject    grade    student_no                    row
5    旺旺      数学            90         002                      1
2    章三      数学            80         001                      2
11   娜吒      数学            67         004                      3
6    旺旺      英语            100        002                       1
15   雷震子    英语            97         005                       2
3    章三      英语            90         001                       3
13   雷震子    语文            89         005                       1
4    旺旺      语文            80         002                       2
10   娜吒      语文            78         004                       3

 

--sql server sql语句
WITH TableOrder AS(
select  t.*,row_number() over(partition by t.[Subject] order by t.grade desc) row from [Tb_Student] t) 
SELECT * FROM TableOrder  where row<=3;

--oracle sql
select * from (
select  t.*,row_number() over(partition by t.[Subject] order by t.grade desc) rownum from [Tb_Student] t) where rownum<=3;