救命：求一SQL，帮帮小弟!!!!!!!

Question

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

Answer 1

sigh~~，这个问题我见过已经不下4次了，:(

Answer 2

Select * from  StudentAchievement A Where  Exists(
select 1 from StudentAchievement 
Where CourseID=A.CourseID And Achievement>A.Achievement 
Having Count(*)<=1
)
Order by CourseID,Achievement Desc或者:Select *
From StudentAchievement a
Where StudentID in (Select Top 2 StudentID 
                    From StudentAchievement
                    Where CourseID=a.CourseID 
                    Order by Achievement desc)
order by CourseID,Achievement desc

Answer 3

最好加个参数　防止有同分
select 
    A.*
from
    StudentAchievement A
where
    A.StudentID in(select top 2 with ties StudentID from StudentAchievement where CourseID=A.CourseID order by Achievement desc)
order by
    A.StudentID,A.CourseID,a.Achievement

Answer 4

http://blog.csdn.net/xluzhong/articles/384351.aspx

Answer 5

这个问题见过很多次了为什么提问之前不去search一下呢？