现在有学生成绩表，结构如下

Create Table StuAchievement (      StuId varchar(8),      CourseId varchar(10),      Achievement int)

数据就自己动动手录入吧

SELECT * FROM StuAchievement AWHERE EXISTS(        SELECT TOP 1 * FROM StuAchievement b         WHERE a.CourseId=b.CourseId AND b.Achievement >a.Achievement         HAVING COUNT(0)<=1)ORDER BY a.CourseId,a.Achievement DESC

或者

SELECT * FROM StuAchievement AWHERE NOT EXISTS(        SELECT TOP 1 * FROM StuAchievement b         WHERE a.CourseId=b.CourseId AND b.Achievement >a.Achievement         HAVING COUNT(0)>=2)ORDER BY a.CourseId,a.Achievement DESC

或者

--运用CTEWITH temp AS (    SELECT StuId,CourseId,ROW_NUMBER() OVER (PARTITION BY CourseId ORDER BY CourseId,Achievement DESC) AS rowId    FROM StuAchievement)SELECT * FROM StuAchievement a,temp b WHERE a.StuId=b.StuId AND a.CourseId=b.CourseId AND b.rowId<=2ORDER BY a.CourseId,a.Achievement DESC