MYSQL 50题其三

11. 查询没有学全全部课程的同学

其实就是查询学生学习的课程的总数和总的课程总数不相等的学生

SELECT s.*
FROM student s
LEFT JOIN score sc
ON s.s_id = sc.s_id
GROUP BY s.s_id HAVING COUNT(s.s_id) != 
(
SELECT COUNT(1) FROM course
)

结果：

12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

这个题目可以理解为先找到01同学学习的总课程，其他的学生学的课程只有有一个在这个总课程里面就可以。

select * 
from student 
where s_id in
(
 select distinct a.s_id 
 from score a 
where a.c_id 
in(select a.c_id from score a where a.s_id='01')
	);

结果：

13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
    解决这个问题之前，我们先学习一个函数 叫做 GROUP_CONCAT, 他可以将相同的行连接起来，并且可以指定连接的顺序(正序还是倒序)，但是需要后面跟group by 或者条件
    比如说：

select GROUP_CONCAT(a.c_id ORDER BY a.c_id) from score  a GROUP BY a.s_id

结果：

还有另外两个函数 concat 和 concat-ws，concat指的是将多个值连接起来，concat-ws和concat功能一样，但是可以指定分隔符：

select concat(s.s_id,s.s_name) from student s

结果：

select CONCAT_WS('-',s.s_id,s.s_name) from student s

结果：

言归正传继续说那个题目，既然可以将结果连接起来，那么我们可以先将01同学学的的课程都连接起来，其他的同学学习的课程也连接起来，和01同学结果一样的，那就是和01同学学的课程一样。

SELECT  s.*
FROM student s 
LEFT JOIN score sc 
ON s.s_id = sc.s_id
GROUP BY s.s_id HAVING GROUP_CONCAT(sc.c_id ORDER BY sc.c_id) = 
(SELECT GROUP_CONCAT(a.c_id ORDER BY a.c_id) FROM score a WHERE a.s_id='01')

结果：

14. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    关联 分数表，课程表和老师表，根据条件张三，查询出所有教过的学生id列表，在找出不在这个列表中的学生即可

SELECT * FROM student s WHERE s.s_id NOT IN
(
SELECT sc.s_id FROM score sc 
JOIN course c ON sc.c_id = c.c_id
JOIN teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三'
)

结果：

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    思路是先根据不及格过滤，剩下的进行分组，并统计科目的数量，找到大于等于2的同学的sid

SELECT sc.s_id FROM score sc 
WHERE sc.s_score <60
GROUP BY sc.s_id HAVING COUNT(sc.c_id) >= 2

找到sid了，就可以根据学生表和成绩进行联合查询，并求平均值AVG了

SELECT s.*,AVG(c.s_score)
FROM student s
LEFT JOIN score c
ON s.s_id = c.s_id 
GROUP BY s.s_id HAVING s.s_id IN
(
SELECT sc.s_id FROM score sc 
WHERE sc.s_score <60
GROUP BY sc.s_id HAVING COUNT(sc.c_id) >= 2
)

结果：

未完待续！

欢迎关注我的微信公众号： 北风中独行的蜗牛

参考：https://blog.csdn.net/fashion2014/article/details/78826299
https://www.cnblogs.com/coder-wf/p/11128033.html