SQL面试必会50题

大良说

16题：检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT *
FROM sc
JOIN student st
ON sc.sid = st.sid
WHERE cid = 01 AND score < 60
ORDER BY score DESC

好吧 我看错了 下面的答案不是16题的 这个不小心就容易误导人啊

真爱维纳斯

都错了，你这个只是让选课数量相同，没有证明选课相同

有趣青年

select s_id from score where s_id != 01
group by s_id
having GROUP_CONCAT(c_id) = (select GROUP_CONCAT(c_id) from score where s_id = 01 group by s_id)

上海生

第十五题：
select student.s_id,student.s_name,avg(s_score) from student inner join score on student.s_id = score.s_id
where student.s_id in (
select s_id from score where s_score < 60
group by s_id
having count(c_id)>=2)
group by
student.s_id

太公望

补充12题：
select s_id from score
where s_id != '01'
and s_id not in (
select s_id from score where c_id not in ( select c_id from score where s_id = '01'
))
group by s_id
having count(*) = (select count(*) from score where s_id = '01')

笑的人生

19题，由于mysql 8.0才有开窗函数
以下答案基于mysql 5.7
-----------------------------------------------------------------------
select s_id,a.c_id,c_name,s_score,rank from (
select s_id,c_id,s_score,
case
        when c_id = @cid then
                if(@prevRank = s_score,@curRank,@curRank := @curRank + 1)
        else
                @curRank := 1
end as rank,
@cid := c_id,
@prevRank := s_score
from score, (select @cid:=null,@prevRank:=null,@curRank:=0) p
order by c_id,s_score desc) a left join course on a.c_id=course.c_id
-----------------------------------------------------------------------

正义不会缺席

第十题的INNER JOIN改成 LEFT JOIN可以解决这个问题，因为成绩表里没有这个人，INNER JOIN匹配的时候把王菊删除了

抿茗思弈

昨天、今天集中时间，参照答主的B站视频，完成了50题练习。很感谢。

任大俊

41题
select b.* from
                score as b inner join
                (select s_id from Score group by s_id having sum(s_score)/count(s_score) = max(s_score)) as a on b.s_id = a.s_id

雅思贝尔乐幼儿园

第7题可以改良：SELECT score.sid,student.sname from score
LEFT JOIN score as a on score.sid=a.sid
LEFT JOIN student on score.sid=student.sid
WHERE score.cid<>a.cid and score.cid='01' and a.cid='02'
GROUP BY score.sid;