《SQL面试50题》刷题笔记 day4（知识点：ifnull、分组函数group by特殊用法、where on条件表达式）

问题6 查询没有学全所有课程的同学的信息

我在解决这个问题的时候是正向的思维，第一遍我写的是这样的，运行下来没有查到‘王菊’这个学生。

select s.*, a.num from
student s,
(select sid, count(cid) as num from score group by sid) a
where s.sid=a.sid and num<3;

在观察到学生表student中由08号学生而成绩表score中没有08号学生的成绩时，首先想到 left join可以显示出null，然后在此基础上做筛选，于是我通过多层子查询，给出下面的查询语句。

select * from student st where sid in
(select sid_stu from
  (select s.sid as sid_stu, a.sid as sid_sc, a.num 
  from student s 
  left join
  (select sid, count(cid) as num from score group by sid) a 
  on s.sid=a.sid) n
where 
num< (select count(cid) from course) or isnull(num));

这一查询中把之前的3这个数字的得到改成了查询语句，这样语句有拓展性。

其中，内部那个左联结得到的表如下：

+---------+--------+------+
| sid_stu | sid_sc | num  |
+---------+--------+------+
| 01      | 01     |    3 |
| 02      | 02     |    3 |
| 03      | 03     |    3 |
| 04      | 04     |    3 |
| 05      | 05     |    2 |
| 06      | 06     |    2 |
| 07      | 07     |    2 |
| 08      | NULL   | NULL |
+---------+--------+------+

通过学习链接中第4条
https://www.cnblogs.com/mucheng/p/6114885.html
优化我的语句为：

select * from student st where sid in
(select sid_stu from
(select s.sid as sid_stu, a.sid as sid_sc, a.num 
from student s 
left join
(select sid, count(cid) as num from score group by sid) a 
on s.sid=a.sid) n
where ifnull(num,'') != (select count(cid) from course));

ifnull(num,’ ') 即 找到空值null后用空格替换之。
运行结果：

+-----+--------+------------+------+
| sid | sname  | sbirth     | ssex |
+-----+--------+------------+------+
| 05  | 周梅   | 1991-12-01 | 女   |
| 06  | 吴兰   | 1992-03-01 | 女   |
| 07  | 郑竹   | 1989-07-01 | 女   |
| 08  | 王菊   | 1990-01-20 | 女   |
+-----+--------+------------+------+

采用逆向思维的答案是这样的：

SELECT * from student
where sid not in
	(SELECT sid from score GROUP BY sid
	having count(cid) = (select count(cid) 
	from course));

通过这个题了解到：
1、MySQL中分组函数group by用法除本文提到的之外还有其它特殊点（可以省略字段），不太找得到权威的解释，后续有空再思考；
2、学习了查找空值的方法（isnull、is （not）null、！isnull、not isnull、=null用法 ）；
3、on和where子句中都不可以用聚集函数做条件表达式；
4、在通过过滤选择出不具有特定值的行时，你可能希望返回具有null值的行，但是不行，因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有null行。