关于es6:神奇的-SQL-之-HAVING-→-容易被轻视的主角

一天，楼主和隔壁小男孩一起坐电梯，中途进来一位高挑的美女，她牵着一条银白的贵宾犬

小男孩看着这条银白的贵宾犬，甚是喜爱，说道：阿姨，我能摸下这个狗狗吗？

美女：叫姐姐

小男孩抬头看了下贵宾犬，虽说有点不乐意，但还是说道：阿姨，我能摸下这个姐姐吗？

楼主想忍住，但切实是忍不住了，哈哈哈 …

初识 HAVING

对于 SQL 中的 HAVING，置信大家都不生疏，它往往与 GROUP BY 配合应用，为聚合操作指定条件

说到指定条件，咱们最先想到的往往是 WHERE 子句，但 WHERE 子句只能指定行的条件，而不能指定组的条件（这外面有个“阶”的概念，能够查阅：神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能间接援用原表中的列），因而就有了 HAVING 子句，它用来指定组的条件。咱们来看个具体示例就分明了。

咱们有 学生班级表（tbl_student_class）以及 数据如下：

DROP TABLE IF EXISTS tbl_student_class; CREATE TABLE tbl_student_class (
id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘ 自增主键 ’,
sno varchar(12) NOT NULL COMMENT ‘ 学号 ’,
cno varchar(5) NOT NULL COMMENT ‘ 班级号 ’,
cname varchar(50) NOT NULL COMMENT ‘ 班级名 ’, PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’ 学生班级表 ’; — —————————- — Records of tbl_student_class — —————————-
INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190607001’, ‘0607’, ‘ 影视 7 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190607002’, ‘0607’, ‘ 影视 7 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190608003’, ‘0608’, ‘ 影视 8 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190608004’, ‘0608’, ‘ 影视 8 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190609005’, ‘0609’, ‘ 影视 9 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190609006’, ‘0609’, ‘ 影视 9 班 ’); INSERT INTO tbl_student_class(sno, cno, cname) VALUES (‘20190609007’, ‘0609’, ‘ 影视 9 班 ’);

咱们要查问  学生人数为 3 的班级，这就须要用到 HAVING 了，置信大家都会写

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

如果咱们不应用 HAVING，会是什么样呢

能够看到，除了数量等于 3 的班级之前，其余的班级也被查出来了

咱们能够简略总结下：WHERE 先过滤出行，而后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出咱们须要的组

HAVING 子句的形成因素

既然 HAVING 操作的对象是组，那么其应用的因素是有肯定限度的，可能应用的因素有 3 种：常数、聚合函数 和 聚合键，聚合键也就是 GROUP BY 子句中指定的列名

示例中的 HAVING COUNT() = 3，COUNT() 是聚合函数，3 是常数，都在 3 因素之中；如果有 3 因素之外的条件，会是怎么样呢

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

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

[Err] 1054 – Unknown column ‘cname’ in ‘having clause’

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

聚合后的这个后果并没有 cname 这个列，那么通过这个列来进行条件解决，当然就报错了啦

仔细的小伙伴应该曾经发现，HAVING 子句的形成因素和蕴含 GROUP BY 子句时的 SELECT 子句的形成因素是一样的，都是只能蕴含 常数、聚合函数 和 聚合键 

HAVING 的魅力

HAVING 子句是 SQL 里一个十分重要的性能，是了解 SQL 面向汇合这一实质的要害。上面联合具体的案例，来感触下 HAVING 的魅力

是否存在缺失的编号

tbl_student_class 表中记录的 id 是间断的（id 的起始值不肯定是 1），咱们去掉其中 3 条

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

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

数据量少，咱们一眼就能看进去，然而如果数据量上百万行了，用眼就看不出来了吧

不绕圈子了，我就间接写了，置信大家都能看懂（记得和本人想的比照一下）

SELECT ‘ 存在缺失的编号 ’ AS gap FROM tbl_student_class HAVING COUNT(*) <> MAX(id) – MIN(id) + 1;

下面的 SQL 语句里没有 GROUP BY 子句，此时整张表会被聚合为一组，这种状况下 HAVING 子句也是能够应用的（HAVING 不是肯定要和 GROUP BY 一起应用）

写的更谨严点，如下（没有 HAVING，不是配角，看一眼就好）

— 无论如何都有后果返回
SELECT CASE WHEN COUNT(*) = 0 THEN ‘ 表为空 ’

WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的编号'
ELSE '间断' END AS gap FROM tbl_student_class;

那如何找出缺失的编号了，欢送评论区留言

求众数

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

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

平均工资达到了 149000 元，乍一看如同毕业生大多都能拿到很高的工资。然而这个数字背地却有一些玄机，因为功夫巨匠李小龙在这一届毕业生中，因为他出众的薪资，将大家的均匀薪资拉升了一大截

简略地求平均值有一个毛病，那就是很容易受到离群值（outlier）的影响。这种时候就必须应用更能精确反映出群体趋势的指标——众数（mode）就是其中之一

那么如何用 SQL 语句来求众数了，咱们往下看

— 应用谓词 ALL 求众数
SELECT salary, COUNT() AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT() >= ALL (SELECT COUNT(*) FROM tbl_student_salary GROUP BY salary);

后果如下

ALL 谓词用于 NULL 或空集时会呈现问题，咱们能够用极值函数来代替；这里要求的是元素数最多的汇合，因而能够用 MAX 函数

— 应用极值函数求众数
SELECT salary, COUNT() AS cnt FROM tbl_student_salary GROUP BY salary HAVING COUNT() >= (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM tbl_student_salary GROUP BY salary

    ) TMP
) ;

求中位数

当平均值不可信时，与众数一样常常被用到的另一个指标是中位数（median）。它指的是将汇合中的元素按升序排列后恰好位于正中间的元素。如果汇合的元素个数为偶数，则取两头两个元素的平均值作为中位数

表 tbl_student_salary 有 10 条记录，那么 张三三, 60000 和 李四, 50000 的平均值 55000 就是中位数

那么用 SQL，该如何求中位数呢？做法是，将汇合里的元素依照大小分为上半局部和下半局部两个子集，同时让这 2 个子集独特领有汇合正中间的元素。这样，独特局部的元素的平均值就是中位数，思路如下图所示

像这样须要依据大小关系生成子集时，就轮到非等值自连贯出场了

— 求中位数的 SQL 语句：在 HAVING 子句中应用非等值自连贯
SELECT AVG(DISTINCT salary) FROM (SELECT T1.salary FROM tbl_student_salary T1, tbl_student_salary T2 GROUP BY T1.salary — S1 的条件

HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
-- S2 的条件
AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2 ) TMP;

这条 SQL 语句的要点在于比拟条件 >= COUNT(*)/2 里的等号，加上等号并不是为了清晰地离开子集 S1 和 S2，而是为了让这 2 个子集领有独特局部

如果去掉等号，将条件改成 > COUNT(*)/2，那么当元素个数为偶数时，S1 和 S2 就没有独特的元素了，也就无奈求出中位数了；加上等号是为了写出通用性更高的 SQL

查问不蕴含 NULL 的汇合

假如咱们有一张学生报告提交记录表：tbl_student_submit_log

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

学生提交报告后，submit_date 列会被写入日期，而提交之前是 NULL

当初咱们须要从这张表里找出哪些学院的学生全副都提交了报告，这个 SQL 该怎么写？

如果只是用 WHERE submit_date IS NOT NULL 条件进行查问，那文学院也会被蕴含进来，后果就不正确了

正确的做法应该先以 dept 进行分组（GROUP BY），而后对组进行条件的过滤，SQL 如下

SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = COUNT(submit_date);

这里其实用到了 COUNT 函数，COUNT(*) 能够用于 NULL，而 COUNT(列名) 与其余聚合函数一样，要先排除掉 NULL 的行再进行统计

当然，应用 CASE 表达式也能够实现同样的性能，而且更加通用

SELECT dept FROM tbl_student_submit_log GROUP BY dept HAVING COUNT(*) = SUM(CASE WHEN submit_date IS NOT NULL THEN 1

    ELSE 0 END );

其余

不仅仅只是如上的那些场景实用于 HAVING，还有很多其余的场景也是须要用到 HAVING 的，有趣味的能够去翻阅《SQL 进阶教程》

聚合键条件的归属

咱们来看个乏味的货色，还是用表：tbl_student_class

咱们发现，聚合键所对应的条件既能够写在 HAVING 子句当中，也能够写在 WHERE 子句当中

尽管条件别离写在 HAVING 子句和 WHERE 子句当中，然而条件的内容，以及返回的后果都完全相同，因而，很多小伙伴就会感觉两种书写形式都没问题

单从后果来看，的确没问题，但其中有一种属于偏离了 SQL 标准的非正规用法，举荐做法是：聚合键所对应的条件应该书写在 WHERE 子句中，理由有二

语义更清晰

WHERE 子句和 HAVING 子句的作用是不同的；后面曾经说过，HAVING 子句是用来指定“组”的条件的，而“行”所对应的条件应该写在 WHERE 子句中，这样一来，写进去的 SQL 语句岂但能够分清两者各自的性能，而且了解起来也更容易

执行速度更快

应用 COUNT 等函数对表中数据进行聚合操作时，DBMS 外部进行排序解决，而排序解决会大大增加机器的累赘，从而升高处理速度；因而，尽可能减少排序的行数，能够进步处理速度

通过 WHERE 子句指定条件时，因为排序之前就对数据进行了过滤，那么就缩小了聚合操作时的须要排序的记录数量；而 HAVING 子句是在排序之后才对数据进行分组的，与在 WHERE 子句中指定条件比起来，须要排序的数量就会多得多

另外，索引是 WHERE 依据速度劣势的另一个无利反对，在 WHERE 子句指定条件所对应的列上创立索引，能够大大提高 WHERE 子句的处理速度

总结

1、集合论

集合论是 SQL 语言的根基，只有从汇合的角度来思考，能力明确 SQL 的弱小威力

学习 HAVING 子句的用法是帮忙咱们顺利地忘掉面向过程语言的思考形式并了解 SQL 面向汇合个性的最为无效的办法

2、HAVING 子句的因素

3 个因素：常数、聚合函数 和 聚合键

HAVING 大多数状况下和联合 GROUP BY 来应用，但不是肯定要联合 GROUP BY 来应用

3、SQL 的执行程序

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