sql-选出每门课程的前两名的学生编号，课程编号，成绩并排序,谢谢

[房贷首付3]

在SQL Server 数据库中，有一个表StudentAchievement（学生成绩）,它有三个字段：StudentID(varchar(8),学生编号)，CourseID(varchar(10),课程编号)，Achievement(int ，成绩)，写一条SQL语句，筛选出每门课程的前两名的学生编号，课程编号，成绩并排序
帮忙写条sql吧，或者给点思路，谢谢，
top 2 和 group 怎么同时用？

[房贷首付3]

在线等啊，急！

[鼓手]

SELECT TOP 4 Achievement, StudentID, CourseID
FROM StudentAchievement
GROUP BY Achievement, CourseID, StudentID
ORDER BY MAX(Achievement) DESC

[鼓手]

下面这样就可以了。

SELECT TOP 4 Achievement, StudentID, CourseID
FROM StudentAchievement
GROUP BY Achievement, CourseID, StudentID
ORDER BY Achievement DESC

[房贷首付3]

不行啊，这样只能选出前4条吧，而不是每一科目的前两名
还望大侠帮忙

[lovefootball]

楼上的写法只适合两门课程吧

[lovefootball]

楼上的写法只适合两门课程吧

[lovefootball]

楼上的写法只适合两门课程吧

[房贷首付3]

wen3062(鼓手) 的写法如果一门课程记录超过了2个，也不能适合2们课程的，
现在急啊，问题好像不是用top和group，order就能解决的，请教各位了！！！

[lovefootball]

Select CourseID,Max(Achievement)
From StudentAchievement
Where Achievement Not In (Select Max(Achievement) From StudentAchievement Group By CourseID)
Group By CourseID
Union
(Select CourseID,Max(Achievement)
From StudentAchievement
Where Achievement In (Select Max(Achievement) From StudentAchievement Group By CourseID) Group By CourseID)

你在用join把学生id加进去就可以了

[房贷首付3]

试试先谢谢了

[房贷首付3]

谢谢！