50道题帮你快速复习MySQL(五)

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct s1.*
from Score as s1,
     Score as s2
where s1.c_id != s2.c_id
  and s1.s_score = s2.s_score;

运行结果:

42.查询每门功成绩最好的前两名

select *
from (select s.c_id, s.s_score, rank() over (partition by c_id order by s_score desc) as 'rk'
      from Score as s) as t
where rk <= 2;

运行结果:

43.统计每门课程的学生选修人数（超过5人的课程才统计）。

补充:要求输出课