【SQL进阶教程】HAVING子句的力量

Posted on | In

表的记录是没有顺序的，而且SQL也没有排序的运算符。SQL会将多条记录作为一个集合来处理。

寻找缺失的编号

假设现有一张带有“连续编号”列的表，如表SeqTbl所示。查询这张表里是否存在数据丢失。

nktNZ9.jpg

如果将表整体看作一个集合，就可以像下面这样解决这个问题。

1
2
3
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

nkdtl6.png

如果这个查询结果有1行，说明存在缺失的编号；如果1行都没有，说明不存在缺失的编号。

接下来，再来查询一下缺失编号的最小值。像下面这样写SQL语句。

1
2
3
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq + 1) NOT IN (SELECT seq FROM SeqTbl);

nk0RoQ.png

如果没有缺失的编号，则查询到的结果是最大编号8的下一个编号9。

然而这两个查询还不够周全，并不能涵盖所有的情况。

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

nkRt1g.jpg

就上面的表Graduates来说，众数就是10000和20000这两个值。接下来思考一下如何用SQL求众数。

1
2
3
4
5
6
7
-- 谓词
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*) 
                        FROM Graduates
                        GROUP BY income);

nkWBKH.png

可以用极值函数来代替。

1
2
3
4
5
6
7
8
-- 极值函数
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子句进行自连接：求中位数

前面的表Graduates里有10行数据，所以取“史密斯，20000”和“劳伦斯，15000”的平均值17500作为中位数。来思考一下如何来查询位于集合正中间的元素。
做法是，将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这2个子集共同拥有集合正中间的元素。这样，共同部分的元素的平均值就是中位数。

1
2
3
4
5
6
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
      FROM Graduates T1, Graduates T2
      GROUP BY T1.income
      HAVING SUM(CASE WHEN T1.income <= T2.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
             AND SUM(CASE WHEN T1.income >= T2.income THEN 1 ELSE 0 END) >= COUNT(*) / 2) TMP;

查询不包含NULL的集合

这里有一张存储了学生提交报告的日期的表Students，如下所示。

nkOiFg.jpg

现在需要从这张表里找出哪些学院的学生全部都提交了报告。

1
2
3
4
5
-- COUNT函数
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

nkOwkD.png

当然使用CASE表达式也可以实现同样的功能。

1
2
3
4
5
-- CASE表达式
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

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

假设有这样两张表：全国连锁折扣店的商品表Items，以及各个店铺的库存管理表ShopItems。

nkXS39.jpg

要查询的是囊括了表Items中所有商品的店铺。SQL语句可以像下面这样写。

1
2
3
4
5
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);

nAelNQ.png

接下来把条件变一下，只选择没有剩余商品的店铺。解决这个问题需要使用外连接。

1
2
3
4
5
6
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);

nAeo8A.png

nAeqDf.png