SQL进阶教程——HAVING子句的力量（第四章）

1. 寻找缺失编号

seq	name
1 迪克
2	安
3	莱露
5	卡
6	玛丽
8	本

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

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

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

• Graduate（毕业生表）

name(名字)	income(收入)
桑普僧	400000
麦克	30000
怀特	20000
阿诺德	2000
史密斯	20000
劳伦斯	15000
哈德逊	15000
肯特	10000
贝克	10000
斯科特	10000

--求众数的SQL语句：使用谓词
SELECT income,COUNT(*) AS cnt
FROM Graduate
GROUP BY income
HAVING COUNT(*) >= ALL(SELECT COUNT(*)
					FROM Graduate
					GROUP BY income);

--ALL谓词用于NULL或空集时会出现问题，可以用极值函数来代替
--用于NULL时永远不会返回结果，用于空集返回原表所有记录
SELECT income,COUNT(*) AS cnt
FROM Graduate
GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(cnt)		
					FROM (SELECT COUNT(*) AS cnt
						FROM Graduate
						GROUP BY income) TMP );

3. 用HAVING子句进行自连接：求中位数

SELECT AVG (DISTINCT income)
FROM (SELECT t1.income FROM Graduates t1,Graduates t2
	GROUP BY t1.income
				--s1 的条件
				HAVING SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END)
										>= COUNT(*) / 2
			  --s2的条件
				AND SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END)
										>= COUNT(*) / 2) TMP;

注意：此处逻辑很复杂，请参考 https://blog.csdn.net/yjw123456/article/details/100988587

4. 查询不包含NULL的集合

• Student

student_id（学号 ID）	dpt（学院）	sbmt_date（提交日期）
100	理学院	2005-10-10
101	理学院	2005-09-22
102	文学院
103	文学院	2005-09-10
200	文学院	2005-09-22
201	工学院
202	经济学院	2005-09-25

-- 查询“提交日期”列内不包含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 NULLTHEN 1 
					ELSE 0 END);

4. 用关系除法进行购物篮分析

购物篮分析是市场分析领域常用的一种分析手段，用来发现“经常被一起购买的商品”。

• Items

iitem（商品）
啤酒
纸尿裤
自行车

• ShopItems

shop（店铺）	item（商品）
仙台	啤酒
仙台	纸尿裤
仙台	自行车
仙台	窗帘
东京	啤酒
东京	纸尿裤
东京	自行车
大阪	电视
大阪	纸尿裤
大阪	自行车

--查询啤酒，纸尿裤和自行车同时在库的店铺
SELECT SI.shop,
 FROM ShopItems SI,Items I
WHERE SI.item = I.item
 GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);

--精准关系除法：使用外连接和COUNT函数
SELECT SI.shop,
 FROM 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. 表不是文件，记录也没有顺序，所以SQL不进行排序
2. SQL不是面向过程语言，没有循环，条件分支，赋值操作
3. SQL通过不断生成子集来求得目标集合。SQL不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考
4. GROUP BY子句可以用来生成子集
5. WHERE子句用来调查集合元素的性质，而HAVING子句来调查集合本身的性质