select * from 
(select no=row_number() over(partition by 课程 order by 成绩 desc),* from tb) t
where no<4

------解决方案--------------------
SQL code
select * from tab a
where (select count(*) from tab where 课程 = a.课程 and 成绩 >= a.成绩) <= 3

------解决方案--------------------
SQL code

;WITH
DD AS(
SELECT C#,GRADE,ROW_NUMBER()OVER(PARTITION BY C# ORDER BY GRADE DESC) PM
FROM SC
GROUP BY C#,GRADE
)
SELECT *
FROM DD
WHERE PM<=3
---
/*结果：这是我的学生数据库 跟你要的一个样，这是这里保存的是课程号 你把它改为课程名就OK
C#         GRADE                                   PM
---------- --------------------------------------- --------------------
042077     85.0                                    1
042077     84.0                                    2
042077     81.0                                    3
042078     92.0                                    1
042078     85.0                                    2
042078     80.0                                    3
042079     90.0                                    1
042079     85.0                                    2