一张表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;