神奇的 SQL 之 HAVING 一个容易被忽视的主角!
来源:SegmentFault
时间:2023-01-11 17:39:09 267浏览 收藏
怎么入门数据库编程?需要学习哪些知识点?这是新手们刚接触编程时常见的问题;下面golang学习网就来给大家整理分享一些知识点,希望能够给初学者一些帮助。本篇文章就来介绍《神奇的 SQL 之 HAVING 一个容易被忽视的主角!》,涉及到MySQL、Java,有需要的可以收藏一下
送大家以下java学习资料,文末有领取方式
初识 HAVING
关于 SQL 中的 HAVING,相信大家都不陌生,它往往与 GROUP BY 配合使用,为聚合操作指定条件
说到指定条件,我们最先想到的往往是 WHERE 子句,但 WHERE 子句只能指定行的条件,而不能指定组的条件,因此就有了 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 = 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
image-20210712181154110
我们发现,聚合键所对应的条件既可以写在 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 子句是指定组所对应的条件
到这里,我们也就讲完了《神奇的 SQL 之 HAVING 一个容易被忽视的主角!》的内容了。个人认为,基础知识的学习和巩固,是为了更好的将其运用到项目中,欢迎关注golang学习网公众号,带你了解更多关于mysql的知识点!
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
-
259 收藏
-
411 收藏
-
476 收藏
-
312 收藏
-
244 收藏
-
195 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习