SQL进阶教程——HAVING子句又回来了（第十章）

1. 前言

学习HAVING子句的用法是帮主我们顺序的忘掉面向过程语言的思考方式并理解SQL面向集合特性的有效的方法。这是因为，HAVING子句的处理对象是集合而不是记录，所以只有习惯了面向集合的思考方式，才能真正的学好它

2. 各位，全体点名

• Teams

member（队员）	team_id（队伍编号 ID）	status（状态）
乔	1	待命
肯	1	出勤中
米克	1	待命
卡伦	2	出勤中
凯斯	2	休息
简	3	待命
哈特	3	待命
迪克	3	待命
贝斯	4	待命
阿伦	5	出勤中
罗伯特	5	休息
卡根	5	待命

-- 用谓词表达全称量化命题
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS
(SELECT *
	FROM Teams T2
	WHERE T1.team_id = T2.team_id
	AND status <> '待命' 
);
-- “所有队员都处于待命状态”＝“不存在不处于待命状态的队员”


-- 用集合表达全称量化命题(1)
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命' THEN 1 ELSE 0 END);

--如果元素最大值和最小值相等，那么这个集合中肯定只有一种值
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待命'
AND MIN(status) = '待命';

-- 列表显示各个队伍是否所有队员都在待命
SELECT team_id, 
CASE WHEN MAX(status) = '待命' AND MIN(status) = '待命'
THEN '全都在待命'
ELSE '队长！人手不够' END AS status
FROM Teams
GROUP BY team_id;

3. 单重集合和多重集合

• Materials

center（生产地）	receive_date（入库日期）	material（材料）
东京	2007-4-01	锡
东京	2007-4-12	锌
东京	2007-5-17	铝
东京	2007-5-20	锌
大阪	2007-4-20	铜
大阪	2007-4-22	镍
大阪	2007-4-29	铅
名古屋	2007-3-15	钛
名古屋	2007-4-01	钢
名古屋	2007-4-24	钢
名古屋	2007-4-24	钢
名古屋	2007-5-02	镁
名古屋	2007-5-10	钛
福冈	2007-5-10	锌
福冈	2007-5-28	锡

-- 选中材料存在重复的生产地
SELECT center
FROM Materials
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

-- 把条件移到SELECT 子句中，获取具体的名称

SELECT center,
CASE WHEN COUNT(material) <> COUNT(DISTINCT material) THEN '存在重复'
ELSE '不存在重复' END AS status
FROM Materials
GROUP BY center;

--group by 划分子集

-- 存在重复的集合：使用EXISTS
--过将 HAVING 改写成 EXISTS 的方式来解决（查询每天重复情况）
SELECT center, material
FROM Materials M1
WHERE EXISTS
(SELECT *
	FROM Materials M2
	WHERE M1.center = M2.center
	AND M1.receive_date <> M2.receive_date
	AND M1.material = M2.material
);
--用 EXISTS 改写后的 SQL 语句也能够查出重复的具体是哪一种材料；
--如果想要查出不存在重复材料的生产地有哪些，只需要把 EXISTS 改写为 NOT EXISTS 就可以了。

4.寻找缺失的编号：升级版

-- 如果有查询结果，说明存在缺失的编号：只调查数列的连续性
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1 ;


-- 不论是否存在缺失的编号都返回一行结果
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1 THEN '存在缺失的编号'
ELSE '连续' END AS gap
FROM SeqTbl;


--查找最小的缺失编号：表中没有1时返回1
SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 --最小值不是1时返回1
			THEN 1
			ELSE (SELECT MIN(seq+1)
					FROM SeqTbl S1
				  WHERE NOT EXISTS
				  	(SELECT * 
				  		FROM SeqTbl S2
				  	WHERE s2.seq = s1.seq + 1)) 
FROM SeqTbl;

本节要点：

1. 在SQL中指定搜索条件时，最重要的是搞清楚搜索的实体是集合还是集合的元素
2. 如果一个实体对应着一行数据——>那么就是元素，所以使用WHERE子句
3. 如果一个实体对应着多行数据——>那么就是集合，所以使用HAVING子句
4. HAVING子句可以通过聚合函数（特别是极值函数）针对集合指定各种条件
5. 如果通过CASE表达式生成特征函数，那么无论多么复杂的条件都可以描述