我自己解决了: select * from (select rank() over(partition by course_number order by score desc ) rk, sc.* from sc) t where t.rk<4;
这样是不是比较快点 with TT as ( select student_number, course_number, score, row_number()over(partition by course_number order by score desc) ranks from sc ) select student_number, course_number, score from tt where ranks <= 3;
考虑并列,用rank或者dense_rank
select *
from
(select rank() over(partition by course_number order by score desc ) rk,
sc.* from sc) t
where t.rk<4;
with TT as
(
select student_number, course_number, score, row_number()over(partition by course_number order by score desc) ranks from sc
)
select student_number, course_number, score from tt where ranks <= 3;