select * from table1 A where 学生ID in ( select top 3 学生ID from table1 where 课程ID=A.课程ID order by 分数 desc)
declare @a table( SID int, CID int, SCORE int )insert into @a select 1,1,90 union all select 1,2,89 union all select 1,3,70 union all select 2,1,60 union all select 2,2,70 union all select 2,3,90 union all select 3,1,85 union all select 3,2,89 union all select 3,3,90 union all select 4,1,70 union all select 4,2,90 union all select 4,3,68 union all select 5,1,81 union all select 5,2,89 union all select 5,3,71select * from @a a where 3>(select count(1) from @a where a.CID=CID and a.Score<Score) order by CID,SCORE desc,SID/*SID CID SCORE ----------- ----------- ----------- 1 1 90 3 1 85 5 1 81 4 2 90 1 2 89 3 2 89 5 2 89 2 3 90 3 3 90 5 3 71*/
where 学生ID in ( select top 3 学生ID from table1 where 课程ID=A.课程ID order by 分数 desc)
SID int,
CID int,
SCORE int
)insert into @a
select 1,1,90 union all
select 1,2,89 union all
select 1,3,70 union all
select 2,1,60 union all
select 2,2,70 union all
select 2,3,90 union all
select 3,1,85 union all
select 3,2,89 union all
select 3,3,90 union all
select 4,1,70 union all
select 4,2,90 union all
select 4,3,68 union all
select 5,1,81 union all
select 5,2,89 union all
select 5,3,71select *
from @a a
where 3>(select count(1) from @a where a.CID=CID and a.Score<Score)
order by CID,SCORE desc,SID/*SID CID SCORE
----------- ----------- -----------
1 1 90
3 1 85
5 1 81
4 2 90
1 2 89
3 2 89
5 2 89
2 3 90
3 3 90
5 3 71*/