SQL笔记| HAVING 子句的力量

很多人可能并没有深刻地认识到HAVING子句的价值，它不仅仅是SQL里一个非常重要的功能，还是理解SQL的本质“面向集合”的关键，应用范围非常广泛。我们通过学习HAVING子句的用法，进而理解面向集合语言的特性——以集合为单位进行操作。

寻找缺失的编号

1.对“连续编号”列按升序或者降序进行排序。
2.按照键的升序（或者降序）进行循环，比较每一行或其下一行的seq列的值。

--如果有查询结果，说明存在缺失的编号
SELECT '存在缺失的编号' AS  gap
 FROM SeqTb1
 HAVING COUNT(*) <> MAX(seq);

也许大家注意到了，上面的SQL语句里没有GROUP BY 子句，此时整张表会被聚合成1行。在这种情况下，HAVING子句也是可以使用的。在以前的SQL标准里，HAVING子句必须和GROUP BY 子句一起使用，所以到现在也有人会有这样的误解。但是，按照现在SQL标准来说，HAVING子句是可以单独使用的。不过在这种情况下就不能在SELECT子句里引用原来的表里的列了，瑶妹像示例一样使用常量，要么就得像SELCT COUNT(*)这样使用聚合函数。

-- 查询缺失编号的最小值
SELECT MIN(seq+1） AS gap
 FROM SeqTb1
 WHERE  （seq +1) NOT IN ( SELECT seq FROM  SeqTb1 ) ; 

寻找缺失的编号：升级版

-- 如果有查询结果，说明存在缺失的编号：只调查数列的连续性
SELECT '存在缺失的编号' AS gap
 FROM SeqTb1 
 HAVING COUNT(*) <> MAX(seq)-MIN(seq) +1;
-- 不论是否存在缺失的编号，都返回一行结果
SELECT CASE WHEN COUNT(*) =0 THNE '表为空'
            WHEN COUNT(*) <> MAX(seq)-MIN(seq) +1 THEN '存在缺失的编号' 
	ELSE '连续' END AS gap
 FROM SeqTb1 

用HAVING子句进行子查询：求众数

众数（mode）指的是在群体中出现次数最多的值。思路是将收入相同的毕业生汇总到一个集合里，然后从汇总后的各个集合里找出元素个数最多的集合。

-- 求众数的SQL语句（1）：使用谓词
SELECT income,COUNT(*) AS cnt 
 FROM Graduates
 GROUP BY income
 HAVING COUNT(*) >= ALL(SELECT COUNT(*) FORM Graduates GROUP BY income);
-- 求众数的SQL语句（2）：使用极值函数
SELECT income,COUNT(*) AS cnt 
 FROM Graduates
 GROUP BY income
 HAVING COUNT(*) >= (SELECT MAX(cnt) （ SELECT COUNT(*) AS cnt  FORM Graduates GROUP BY income) TMP );

查询不包括NULL的集合

COUNT函数的使用方法有COUNT()和COUNT(列名)两种，他们的区别有两个：第一个是性能上的区别；第二个是COUNT()可以用于NULL，而COUNT(列名)与其他聚合函数一样，要先排除掉NULL行再进行统计。

-- 查询“提交日期”列内不包括NULL 的学院（1）：使用COUNT函数
SELECT dpt
 FROM Students
 GROUP BY dpt
 HAVING COUNT(*) = COUNT(sbmt_date);
-- 查询“提交日期”列内不包括NULL 的学院（2）：使用CASE表达式
SELECT dpt
 FROM Students
 GROUP BY dpt
 HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END );

在这里，CASE表达式的作用相当于进行判断的函数，用来判断各个元素（=行）是否属于满足了某种条件的集合。这样的函数我们称之为特征函数（characteristic function），或者从定义了结合的角度称之为定义函数。HAVING子句可以用作研究集合性质的工具，特别是在与聚合函数或CASE表达式一起使用时，他具有更强大的威力。另外，当时用HAVING子句分割集合来解决问题时，在纸上画圆的方法效果很好。面向过程语言中使用流程图（线和四边形）来辅助思考，而面向集合语言中则是用圆（维恩图）来辅助思考。

特征函数的应用

-- 查询分数在50分以上男生比女生人数多的班级
SELECT class 
 FROM TestResults
 GROUP BY class
  HAVING SUM(CASE WHEN score >=50 AND sex ='男' THEN 1 ELSE0 RND) 
 	> SUM(CASE WHEN score >=50 AND sex ='女' THEN 1 ELSE0 RND) ； 
-- 查询女生比男生平均分高的班级
SELECT class 
 FROM TestResults
 GROUP BY class
  HAVING AVG(CASE WHEN  sex ='男' THEN score ELSE 0 RND) 
 	> AVG(CASE WHEN AND sex ='女' THEN score  ELSE 0 RND) ； 

根据标准SQL定义，对空集使用AVG函数时，结果会返回NULL。

-- 查询女生比男生平均分高的班级（2）：对空集求平均值后返回NULL
SELECT class 
 FROM TestResults
 GROUP BY class
  HAVING AVG(CASE WHEN  sex ='男' THEN score ELSE NULL RND) 
 	> AVG(CASE WHEN AND sex ='女' THEN score  ELSE NULL RND) ； 

这回，D班男生平均分是NULL。因此不管女生的平均分是多少，D班都会被排除在查询结果之外。这种处理方法和AVG函数的处理逻辑是一致的。
关注集合的性质，反过来说其实就是忽略掉单个元素的特征。再解答上面几道例题时，我们考虑的也是班级整体具有的特点和趋势，至于个人得了多少分，并没有关注。这种在确保成员隐秘性的同时研究整体趋势的思维方式与统计学的方法论不谋而合。

使用HAVING 语句表达全称量化

-- 用谓词表达全称量化命题
SELECT team_id, member 
 FROM Team T1
 WHERE NOT EXISTS ( SELECT * FORM Team T2 WHERE T1.team_id = T2.team_id AND status <>'待命');
-- 用集合表达全称量化命题（1)
SELECT team_id, member 
 FROM Team T1
 GROUP BY team_id 
  HAVING COUNT(*) = SUM( CASE WHEN status ='待命' THEN 1 ELSE 0 END );
-- 用集合表达全称量化命题（2)
SELECT team_id, member 
 FROM Team T1
 GROUP BY team_id 
  HAVING MAX(stauts) = '待命' AND MIN(status) = '待命';
-- 列表显示各个队伍是否所有队员都在待命
SELECT team_id, CASE WHEN MAX(stauts) ='待命' AND MIN(status) = '待命' THEN '全员待命' ELSE '人手不够' END AS status
 FROM Team T1
 GROUP BY team_id ；

用关系除法运算进行购物篮分析

-- 查询Items表中同时在库的店铺 商品
SELECT SI.shop
 FORM ShopItems SI INNER JOIN Items I ON SI.item =  I.item
 GROUP BY SI.shop
 HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);
-- 精确关系除法运算：使用外连接和COUNT函数
SELECT SI.shop
 FORM ShopItems SI LEFT OUTER JOIN Items I ON SI.item =  I.item
 GROUP BY SI.shop
 HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)
	AND COUNT(I.item) = (SELECT COUNT(item) FROM Items);

用于调查集合性质的条件极其用途
1.COUNT(DISTINCT col) = COUNT(col) col列没有重复的值
2.COUNT() = COUNT(col) col列不存在NULL
3.COUNT()=MAX(col) col列是连续的编号（起始值是1）
4.COUNT(*)=MAX(col)-MIN(col) +1 col列是连续的编号（起始值是任意整数）
5.MIN(col)=MAX(col) col列都是相同值，或者是NULL
6.MIN(col)*MAX(col)>0 col列全是正数或全是负数
7.MIN(col)*MAX(col)《0 col列最大值是正数，最小值是负数
8.MIN(ABS(col))=0 col列至少有一个是0
9.MIN(col- 常量）=-MAX(col-常量) col列的最大值和最小值与指定常量等距。
本节要点
1.表不是文件，记录也没有顺序，所以SQL不进行排序。
2.SQL通过不断生成子集来求得目标集合。SQL不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考。
3.GROUP BY子句可以用来生成子集。
4.WHERE子句用来调查集合元素的性质，而HAVING子句用来调查集合本身的性质。
5.在SQL中指定搜索条件时，最重要的是搞清楚搜索的实体是集合还是集合的元素。
如果一个实体对应着一行数据->实体是元素，使用WHERE子句。
如果一个实体对应着多行数据->实体是集合，使用HAVING子句。