1-4 HAVING子句的力量

寻找缺失的编号

判断是否存在缺失的编号

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

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

-- 使用极值函数
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= 
( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt 
FROM Graduates GROUP BY income) TMP ) ;

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

中位数是指的是将集合中的元素按升序排列后恰好位于正中间的元素。如果集合的元素个数为偶数，则取中间两个元素的平均值作为中位数。

这个sql用的是集合思想, 不太理解

-- 求中位数的SQL 语句：在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;

参考网上的例子, 加上自己的思想写的如下

SELECT AVG(income)
FROM (
	SELECT income, ROW_NUMBER() OVER (ORDER BY income) - 1 AS rowIndex
		, (
			SELECT MAX(a.rowIndex)
			FROM (
				SELECT ROW_NUMBER() OVER (ORDER BY income) - 1 AS rowIndex
				FROM graduates
			) a
		) AS maxIndex
	FROM graduates
) g
WHERE rowIndex IN (FLOOR(maxIndex / 2), CEIL(maxIndex / 2));

结果都是

查询不包含NULL的集合

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

求提交日期不包含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);

结果

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

查询的是囊括了表 Items 中所有商品的店铺。即仙台店和东京店。

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) -- 条件1
AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); -- 条件2

本章小节

下面是本节要点。

1. SQL 不是面向过程语言，没有循环、条件分支、赋值操作。
2. SQL 通过不断生成子集来求得目标集合。SQL 不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考。
3. GROUP BY 子句可以用来生成子集。
4. WHERE 子句用来调查集合元素的性质，而 HAVING 子句用来调查集合本身的性质。