Mysql必练50题（第九天）

第四十一题
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select s_id,c_id,s_score from score where s_score in 
(select s_score from score group by s_score having count(*)>1);

第四十二题
查询每门课程成绩最好的前三名

select sc.c_id,c.c_name,s.s_id,s.s_name,sc.s_score 
from student s inner join 
(select s_id, c_id,s_score,
(row_number() over(partition by c_id order by s_score desc)) 
as rank_1 from score) sc
on s.s_id = sc.s_id
inner join course c on sc.c_id = c.c_id
where rank_1 between 1 and 3 order by c_id,sc.s_score desc;

第四十三题
统计每门课程的学生选修人数（超过5人的课程才统计）

select sc.c_id,c.c_name,count(*) as cnt from score sc 
inner join course c on sc.c_id = c.c_id 
group by sc.c_id having cnt>5;

第四十四题
检索至少选修两门课程的学生学号

select s.s_id,count(*) as cnt from student s inner join score sc 
on s.s_id = sc.s_id group by sc.s_id having count(*)>=2;

第四十五题
查询选修了全部课程的学生信息

select s.* from student s inner join score sc 
on s.s_id = sc.s_id group by sc.s_id having count(*)=3;