--假如出现并列时,也只取两个同学的话。 select distinct * from studentgrade as t1 where stuid in (select top 2 stuid from studentgrade as t2 where t1.subid=t2.subid order by t2.grade desc) order by subid, grade desc
select * from StudentGrade a where (select count(1) from studentGrade where subId=a.subId and grade>=a.grade)<=2
select * from StudentGrade t where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1 order by subId,grade desc--结果 /* stuId subId grade ----- ----------- ----------- 001 1 97 003 1 93 003 2 95 002 2 80 004 3 87 003 3 85(6 row(s) affected) */
select distinct *
from studentgrade as t1
where stuid in
(select top 2 stuid
from studentgrade as t2
where t1.subid=t2.subid
order by t2.grade desc)
order by subid, grade desc
where (select count(1) from StudentGrade where subid=t.subid and grade>t.grade)<=1
order by subId,grade desc--结果
/*
stuId subId grade
----- ----------- -----------
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85(6 row(s) affected)
*/