27,580
社区成员
发帖
与我相关
我的任务
分享
select a.CourseID, b.StudentID, b.Achievement
from (select distinct CourseID from StudentAchievement) a
cross apply (select top (2) * from StudentAchievement where CourseID = a.CourseID order by Achievement desc) b
select *
from StudentAchievement t
where Achievement in(select top 2 Achievement
from StudentAchievement
where CourseID=t.CourseID order by Achievement desc)
SELECT CourseID,CourseID,Achievement FROM
(
SELECT (ROW_NUMBER() OVER(PARTITION BY CourseID ORDER BY Achievement desc)) AS rowid,CourseID,CourseID,Achievement FROM StudentAchievement )AS A
WHERE A.rowid IN (1,2)