SQL HAVING

HAVING

关于 SQL 中的 HAVING，它往往与 GROUP BY 配合使用，为聚合操作指定条件。说到指定条件，最先想到的往往是 WHERE 子句，但 WHERE 子句只能指定行的条件，而不能指定组的条件（这里面有个“阶”的概念），因此就有了 HAVING 子句，它用来指定组的条件。来看个具体示例就清楚了。
有学生班级表（tbl_student_class） 以及数据如下 ：

  1. DROP TABLE IF EXISTS tbl_student_class;
  2. CREATE TABLE tbl_student_class (
  3.   id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  4.   sno varchar(12) NOT NULL COMMENT '学号',
  5.   cno varchar(5) NOT NULL COMMENT '班级号',
  6.   cname varchar(50) NOT NULL COMMENT '班级名',
  7.   PRIMARY KEY (id)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表';
  9. -- ----------------------------
  10. -- Records of tbl_student_class
  11. -- ----------------------------
  12. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607001', '0607', '影视7班');
  13. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607002', '0607', '影视7班');
  14. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608003', '0608', '影视8班');
  15. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608004', '0608', '影视8班');
  16. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609005', '0609', '影视9班');
  17. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609006', '0609', '影视9班');
  18. INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609007', '0609', '影视9班');

要查询学生人数为 3 的班级 ，这就需要用到 HAVING 了。

  1. SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;

image.png
如果不使用 HAVING，会是什么样呢？
image.png
可以看到，除了数量等于 3 的班级之前，其他的班级也被查出来了。
可以简单总结下：WHERE 先过滤出行，然后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出需要的组。
image.png

HAVING 子句的构成要素

既然 HAVING 操作的对象是组，那么其使用的要素是有一定限制的，能够使用的要素有 3 种：常数 、 聚合函数 和 聚合键 ，聚合键也就是 GROUP BY 子句中指定的列名。
示例中的 HAVING COUNT() = 3 ， COUNT() 是聚合函数，3 是常数，都在 3 要素之中；如果有 3 要素之外的条件，会是怎么样呢？

  1. SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cname = '影视9班';

执行如上 SQL 会失败，并提示：

  1. [Err] 1054 - Unknown column 'cname' in 'having clause'

在使用 HAVING 子句时，把 GROUP BY 聚合后的结果作为 HAVING 子句的起点，会更容易理解；示例中通过 cno 进行聚合后的结果如下：

cno nums
0607 2
0608 2
0609 3

聚合后的这个结果并没有 cname 这个列，那么通过这个列来进行条件处理，当然就报错了。
HAVING 子句的构成要素和包含 GROUP BY 子句时的 SELECT 子句的构成要素是一样的，都是只能包含 常数 、 聚合函数 和 聚合键。

HAVING 的魅力

HAVING 子句是 SQL 里一个非常重要的功能，是理解 SQL 面向集合这一本质的关键。下面结合具体的案例，来感受下 HAVING 的魅力。

是否存在缺失的编号

tbl_student_class 表中记录的 id 是连续的（id 的起始值不一定是 1），去掉其中 3 条。

  1. DELETE FROM tbl_student_class WHERE id IN(2,5,6);
  2. SELECT * FROM tbl_student_class;

image.png
如何判断是否有编号缺失？

  1. SELECT '存在缺失的编号' AS gap
  2. FROM tbl_student_class
  3. HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;

上面的 SQL 语句里没有 GROUP BY 子句，此时整张表会被聚合为一组，这种情况下 HAVING 子句也是可以使用的（HAVING 不是一定要和 GROUP BY 一起使用），写的更严谨点，如下

  1. -- 无论如何都有结果返回
  2. SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
  3.     WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的编号'
  4.     ELSE '连续' END AS gap
  5. FROM tbl_student_class;

求众数

假设有一张表：tbl_student_salary ，记录着毕业生首份工作的年薪。

  1. DROP TABLE IF EXISTS tbl_student_salary;
  2. CREATE TABLE tbl_student_salary (
  3.   id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  4.   name varchar(5) NOT NULL COMMENT '姓名',
  5.   salary DECIMAL(15,2) NOT NULL COMMENT '年薪, 单位元',
  6.   PRIMARY KEY (id)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='毕业生年薪标';
  8. insert into tbl_student_salary values (1,'李小龙', 1000000);
  9. insert into tbl_student_salary values (2,'李四', 50000);
  10. insert into tbl_student_salary values (3,'王五', 50000);
  11. insert into tbl_student_salary values (4,'赵六', 50000);
  12. insert into tbl_student_salary values (5,'张三', 70000);
  13. insert into tbl_student_salary values (6,'张一三', 70000);
  14. insert into tbl_student_salary values (7,'张二三', 70000);
  15. insert into tbl_student_salary values (8,'张三三', 60000);
  16. insert into tbl_student_salary values (9,'张三四', 40000);
  17. insert into tbl_student_salary values (10,'张三丰', 30000);

平均工资达到了 149000 元，乍一看好像毕业生大多都能拿到很高的工资。然而这个数字背后却有一些玄机，因为功夫大师李小龙在这一届毕业生中，由于他出众的薪资，将大家的平均薪资拉升了一大截。
简单地求平均值有一个缺点，那就是很容易受到离群值（outlier）的影响。这种时候就必须使用更能准确反映出群体趋势的指标——众数（mode）就是其中之一。
那么如何用 SQL 语句来求众数了，往下看：

  1. -- 使用谓词 ALL 求众数
  2. SELECT salary, COUNT(*) AS cnt
  3. FROM tbl_student_salary
  4. GROUP BY salary
  5. HAVING COUNT(*) >= ALL (
  6.     SELECT COUNT(*)
  7.     FROM tbl_student_salary
  8.     GROUP BY salary);

结果如下
image.png
ALL 谓词用于 NULL 或空集时会出现问题，可以用极值函数来代替；这里要求的是元素数最多的集合，因此可以用 MAX 函数。

  1. -- 使用极值函数求众数
  2. SELECT salary, COUNT(*) AS cnt
  3. FROM tbl_student_salary
  4. GROUP BY salary
  5. HAVING COUNT(*) >= (
  6.     SELECT MAX(cnt)
  7.     FROM (
  8.         SELECT COUNT(*) AS cnt
  9.         FROM tbl_student_salary
  10.         GROUP BY salary
  11.         ) TMP
  12.     ) ;

求中位数

当平均值不可信时，与众数一样经常被用到的另一个指标是中位数（median）。它指的是将集合中的元素按升序排列后恰好位于正中间的元素。如果集合的元素个数为偶数，则取中间两个元素的平均值作为中位数。
表 tbl_student_salary 有 10 条记录，那么 张三三, 60000 和 李四, 50000 的平均值 55000 就是中位数。
那么用 SQL，该如何求中位数呢？做法是，将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这 2 个子集共同拥有集合正中间的元素。这样，共同部分的元素的平均值就是中位数，思路如下图所示：
image.png
像这样需要根据大小关系生成子集时，就轮到非等值自连接出场了：

  1. -- 求中位数的SQL 语句：在HAVING 子句中使用非等值自连接
  2. SELECT AVG(DISTINCT salary)
  3. FROM (
  4.     SELECT T1.salary
  5.     FROM tbl_student_salary T1, tbl_student_salary T2
  6.     GROUP BY T1.salary
  7.     -- S1 的条件
  8.     HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
  9.     -- S2 的条件
  10.     AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
  11. ) TMP;

这条 SQL 语句的要点在于比较条件 >= COUNT(*)/2 里的等号，加上等号并不是为了清晰地分开子集 S1 和 S2，而是为了让这 2 个子集拥有共同部分。
如果去掉等号，将条件改成 > COUNT(*)/2 ，那么当元素个数为偶数时，S1 和 S2 就没有共同的元素了，也就无法求出中位数了；加上等号是为了写出通用性更高的 SQL。

查询不包含 NULL 的集合

假设有一张学生报告提交记录表：tbl_student_submit_log

  1. DROP TABLE IF EXISTS tbl_student_submit_log;
  2. CREATE TABLE tbl_student_submit_log (
  3.   id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  4.   sno varchar(12) NOT NULL COMMENT '学号',
  5.   dept varchar(50) NOT NULL COMMENT '学院',
  6.   submit_date DATE COMMENT '提交日期',
  7.   PRIMARY KEY (id)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生报告提交记录表';
  9. insert into tbl_student_submit_log values
  10. (1,'20200607001', '理学院', '2020-12-12'),
  11. (2,'20200607002', '理学院', '2020-12-13'),
  12. (3,'20200608001', '文学院', null),
  13. (4,'20200608002', '文学院', '2020-12-22'),
  14. (5,'20200608003', '文学院', '2020-12-22'),
  15. (6,'20200612001', '工学院', null),
  16. (7,'20200617001', '经济学院', '2020-12-23');

学生提交报告后， submit_date 列会被写入日期，而提交之前是 NULL。
现在需要从这张表里找出哪些学院的学生全部都提交了报告，这个 SQL 该怎么写？
如果只是用 WHERE submit_date IS NOT NULL 条件进行查询，那文学院也会被包含进来，结果就不正确了
正确的做法应该先以 dept 进行分组（GROUP BY），然后对组进行条件的过滤，SQL 如下：

  1. SELECT dept
  2. FROM tbl_student_submit_log
  3. GROUP BY dept
  4. HAVING COUNT(*) = COUNT(submit_date);

这里其实用到了 COUNT 函数，COUNT(*) 可以用于 NULL ，而 COUNT(列名) 与其他聚合函数一样，要先排除掉 NULL 的行再进行统计。
当然，使用 CASE 表达式也可以实现同样的功能，而且更加通用。

  1. SELECT dept
  2. FROM tbl_student_submit_log
  3. GROUP BY dept
  4. HAVING COUNT(*) = SUM(
  5.     CASE WHEN submit_date IS NOT NULL THEN 1
  6.         ELSE 0 END
  7.     );

其他

不仅仅只是如上的那些场景适用于 HAVING，还有很多其他的场景也是需要用到 HAVING 的。

聚合键条件的归属

来看个有趣的东西，还是用表：tbl_student_class

  1. SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cno = '0609';

image.png

  1. SELECT cno, COUNT(*) nums FROM tbl_student_class WHERE cno = '0609' GROUP BY cno;

image.png
可以发现，聚合键所对应的条件既可以写在 HAVING 子句当中，也可以写在 WHERE 子句当中。
虽然条件分别写在 HAVING 子句和 WHERE 子句当中，但是条件的内容，以及返回的结果都完全相同，因此，很多人就会觉得两种书写方式都没问题。
单从结果来看，确实没问题，但其中有一种属于偏离了 SQL 规范的非正规用法，推荐做法是：聚合键所对应的条件应该书写在 WHERE 子句中 ，理由有二。

「语义更清晰」

WHERE 子句和 HAVING 子句的作用是不同的；前面已经说过，HAVING 子句是用来指定“组”的条件的，而“行”所对应的条件应该写在 WHERE 子句中，这样一来，写出来的 SQL 语句不但可以分清两者各自的功能，而且理解起来也更容易。 :::info WHERE 子句 = 指定行所对应的条件
HAVING 子句 = 指定组所对应的条件 :::

「执行速度更快」

使用 COUNT 等函数对表中数据进行聚合操作时，DBMS 内部进行排序处理，而排序处理会大大增加机器的负担，从而降低处理速度；因此，尽可能减少排序的行数，可以提高处理速度。
通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，那么就减少了聚合操作时的需要排序的记录数量；而 HAVING 子句是在排序之后才对数据进行分组的，与在 WHERE 子句中指定条件比起来，需要排序的数量就会多得多。
另外，索引是 WHERE 根据速度优势的另一个有利支持，在 WHERE 子句指定条件所对应的列上创建索引，可以大大提高 WHERE 子句的处理速度。

总结

1、集合论

  • 集合论是 SQL 语言的根基，只有从集合的角度来思考，才能明白 SQL 的强大威力。

  • 学习 HAVING 子句的用法是顺利地忘掉面向过程语言的思考方式并理解 SQL 面向集合特性的最为有效的方法。

    2、HAVING 子句的要素

  • 3 个要素：常数、聚合函数 和 聚合键。

  • HAVING 大多数情况下和结合 GROUP BY 来使用，但不是一定要结合 GROUP BY 来使用。

    3、SQL 的执行顺序

    :::success (8) SELECT (9) DISTINCT (11)
    (1) FROM
    (3) JOIN
    (2) ON
    (4) WHERE
    (5) GROUP BY
    (6) WITH {CUBE | ROLLUP}
    (7) HAVING
    (10) ORDER BY :::

WHERE 子句是指定行所对应的条件，而 HAVING 子句是指定组所对应的条件。