sql 这个语句怎么写呢

Question

sc(sno,cno,grade)
查询每门课程中成绩最高的前五名同学

Answer 1

select cno,sno,grade,rank
from (select cno,sno,grade,row_number() over(partition by cno order by grade desc)rank
from sc)
where rank <= 5
order by 1,4;