在SQL Server 数据库中,有一个表StudentAchievement(学生成绩),它有三个字段:StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int ,成绩),写一条SQL语句,筛选出每门课程的前两名的学生编号,课程编号,成绩并排序。
2005SELECT StudentID, CourseID, Achievement FROM( SELECT StudentID, CourseID, Achievement, DENSE_RANK() OVER(PARTITION BY CourseID ORDER BY Achievement DESC) AS rnk FROM StudentAchievement ) AS A WHERE rnk<=2
2000SELECT A.* FROM StudentAchievement AS A WHERE 2>(SELECT COUNT(*) FROM StudentAchievement WHERE CourseID=A.CourseID AND Achievement>A.Achievement)
StudentID,
CourseID,
Achievement
FROM(
SELECT
StudentID,
CourseID,
Achievement,
DENSE_RANK() OVER(PARTITION BY CourseID ORDER BY Achievement DESC) AS rnk
FROM StudentAchievement
) AS A
WHERE rnk<=2
A.*
FROM StudentAchievement AS A
WHERE 2>(SELECT COUNT(*)
FROM StudentAchievement
WHERE CourseID=A.CourseID
AND Achievement>A.Achievement)