SQL 中 HAVING 常见的使用方法

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

HAVING 子句

始终要记得 SQL是一种基于“面向集合”思想设计的语言 。

1. 寻找缺失的编号

查询这张表里是否存在数据缺失。当前这张表的编号并不是连续的，缺少了 4 和 7（这里给出的列是有序的，实际情景下很有可能是无序的）。

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

如果这个查询结果有 1 行，说明存在缺失的编号；如果 1 行都没有，说明不存在缺失的编号。这是因为，如果用 COUNT(*) 统计出来的行数等于“连续编号”列的最大值，就说明编号从开始到最后是连续递增的，中间没有缺失。如果有缺失，COUNT(*) 会小于 MAX(seq) ，这样 HAVING 子句就变成真了。这个解法只需要 3 行代码，十分优雅。

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

也可以认为是对空字段进行了 GROUP BY 操作，只不过省略了 GROUP BY 子句。如果使用窗口函数时不指定 PARTITION BY 子句，就是把整个表当作一个分区来处理的，思路与这里也是一样的。

2. 查询缺少编号的最小值

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

要注意！

如果表里没有编号 1，那么缺失编号的最小值应该是 1，但是这两条 SQL 语句都不能得出正确的结果
如果表里包含 NULL ，那么这条 SQL 语句也不能得出正确的结果

3. 求众数

-- 求众数的SQL：使用极值函数
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 ) ;

这里使用MAX极值函数而不是ALL谓词是因为极值函数可以避免Null值带来的问题。详细内容可以看一文详解SQL 中的三值逻辑这篇文章。

4. 求中位数

将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这 2 个子集共同拥有集合正中间的元素。

这样，共同部分的元素的平均值就是中位数：

-- 求中位数的SQL 语句：在HAVING 子句中使用非等值自连接
SELECT AVG(DISTINCT income) -- 这里一定要去重后 再求平均
FROM (  
        SELECT T1.income
        FROM Graduates T1, Graduates T2
        GROUP BY T1.income
        -- S1 的条件 小于等于T2的数量大于等于全部的一半
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
        -- S2 的条件 大于等于T2的数量大于等于全部的一半
        AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 
        -- 同时满足  小于等于T2的数量大于等于全部的一半 且 大于等于T2的数量大于等于全部的一半 即说明T2在前后两部分的中间的交集中
) TMP;

5. 查询不包含 NULL 的集合

COUNT 函数的使用方法有 COUNT(*) 和 COUNT( 列名 ) 两种，

它们的区别有两个：

第一个是性能上的区别；第二个是 COUNT(*) 可以用于 NULL ，而 COUNT( 列名 ) 与其他聚合函数一样，要先排除掉NULL 的行再进行统计。
第二个区别也可以这么理解：COUNT(*) 查询的是所有行的数目，而 COUNT( 列名 ) 查询的则不一定是。

现在需要查找哪些学院的学生全部都提交了报告（即理学院、经济学院）。

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

同样可以使用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），或者从定义了集合的角度来将它称为定义函数

6. 关系除法运算

现在需要查询囊括了表 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)

同样也可以写出只包含 Items 中所有商品的店铺（东京店）

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