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

[房贷首付3]

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

[taoxianxue]

没有主键吗？

[房贷首付3]

没有给出，不过我想应该是学生编号StudentID

[房贷首付3]

问题已解决，谢谢大家（感谢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)

[taoxianxue]

学习一下 呵呵

[子陌红尘]

select
a.*
from
StudentAchievement a
where
a.StudentID in (select top 2 StudentID from StudentAchievement where CourseID=a.CourseID order by Achievement desc)

[ruodeer]

agree libin_ftsafe(子陌红尘) ( ) 信誉：100
把StudentAchievement表自身连接 方便！但是楼主没说是升序还是降序，要是升序的话就默认了！
select
a.*
from
StudentAchievement a
where
a.StudentID in (select top 2 StudentID from StudentAchievement where StudentAchievement.CourseID=a.CourseID order by StudentAchievement.chievement desc)