SQL?中?HAVING?常見的使用方法

更新時(shí)間：2022年09月19日 10:06:55 作者：Wang1???????

這篇文章主要介紹了SQL中HAVING常見的使用方法，文章通過圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下

HAVING 子句

始終要記得 SQL是一種基于“面向集合”思想設(shè)計(jì)的語(yǔ)言 。

1. 尋找缺失的編號(hào)

查詢這張表里是否存在數(shù)據(jù)缺失。當(dāng)前這張表的編號(hào)并不是連續(xù)的，缺少了 4 和 7（這里給出的列是有序的，實(shí)際情景下很有可能是無序的）。

-- 如果有查詢結(jié)果，說明存在缺失的編號(hào)
SELECT 1 AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

如果這個(gè)查詢結(jié)果有 1 行，說明存在缺失的編號(hào)；如果 1 行都沒有，說明不存在缺失的編號(hào)。這是因?yàn)?，如果?COUNT(*) 統(tǒng)計(jì)出來的行數(shù)等于“連續(xù)編號(hào)”列的最大值，就說明編號(hào)從開始到最后是連續(xù)遞增的，中間沒有缺失。如果有缺失，COUNT(*) 會(huì)小于 MAX(seq) ，這樣 HAVING 子句就變成真了。這個(gè)解法只需要 3 行代碼，十分優(yōu)雅。

上面的 SQL 語(yǔ)句里沒有 GROUP BY 子句，此時(shí)整張表會(huì)被聚合為一行。這種情況下 HAVING 子句也是可以使用的。在以前的 SQL 標(biāo)準(zhǔn)里，HAVING 子句必須和 GROUP BY 子句一起使用，所以到現(xiàn)在也有人會(huì)有這樣的誤解。但是，按照現(xiàn)在的 SQL 標(biāo)準(zhǔn)來說， HAVING 子句是可以單獨(dú)使用的。不過這種情況下，就不能在 SELECT 子句里引用原來的表里的列了，要么就得像示例里一樣使用常量，要么就得像 SELECT COUNT(*) 這樣使用聚合函數(shù)。

也可以認(rèn)為是對(duì)空字段進(jìn)行了 GROUP BY 操作，只不過省略了 GROUP BY 子句。如果使用窗口函數(shù)時(shí)不指定 PARTITION BY 子句，就是把整個(gè)表當(dāng)作一個(gè)分區(qū)來處理的，思路與這里也是一樣的。

2. 查詢?nèi)鄙倬幪?hào)的最小值

-- 查詢?nèi)笔Ь幪?hào)的最小值
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

要注意！

如果表里沒有編號(hào) 1，那么缺失編號(hào)的最小值應(yīng)該是 1，但是這兩條 SQL 語(yǔ)句都不能得出正確的結(jié)果
如果表里包含 NULL ，那么這條 SQL 語(yǔ)句也不能得出正確的結(jié)果

3. 求眾數(shù)

-- 求眾數(shù)的SQL：使用極值函數(shù)
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極值函數(shù)而不是ALL謂詞是因?yàn)闃O值函數(shù)可以避免Null值帶來的問題。詳細(xì)內(nèi)容可以看一文詳解SQL 中的三值邏輯這篇文章。

4. 求中位數(shù)

將集合里的元素按照大小分為上半部分和下半部分兩個(gè)子集，同時(shí)讓這 2 個(gè)子集共同擁有集合正中間的元素。

這樣，共同部分的元素的平均值就是中位數(shù)：

-- 求中位數(shù)的SQL 語(yǔ)句：在HAVING 子句中使用非等值自連接
SELECT AVG(DISTINCT income) -- 這里一定要去重后 再求平均
FROM (  
        SELECT T1.income
        FROM Graduates T1, Graduates T2
        GROUP BY T1.income
        -- S1 的條件 小于等于T2的數(shù)量大于等于全部的一半
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
        -- S2 的條件 大于等于T2的數(shù)量大于等于全部的一半
        AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2 
        -- 同時(shí)滿足  小于等于T2的數(shù)量大于等于全部的一半 且 大于等于T2的數(shù)量大于等于全部的一半 即說明T2在前后兩部分的中間的交集中
) TMP;

5. 查詢不包含 NULL 的集合

COUNT 函數(shù)的使用方法有 COUNT(*) 和 COUNT( 列名 ) 兩種，

它們的區(qū)別有兩個(gè)：

第一個(gè)是性能上的區(qū)別；第二個(gè)是 COUNT(*) 可以用于 NULL ，而 COUNT( 列名 ) 與其他聚合函數(shù)一樣，要先排除掉NULL 的行再進(jìn)行統(tǒng)計(jì)。
第二個(gè)區(qū)別也可以這么理解：COUNT(*) 查詢的是所有行的數(shù)目，而 COUNT( 列名 ) 查詢的則不一定是。

現(xiàn)在需要查找哪些學(xué)院的學(xué)生全部都提交了報(bào)告（即理學(xué)院、經(jīng)濟(jì)學(xué)院）。

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

同樣可以使用case表達(dá)式

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

在這里，CASE 表達(dá)式的作用相當(dāng)于進(jìn)行判斷的函數(shù)，用來判斷各個(gè)元素（= 行）是否屬于滿足了某種條件的集合。這樣的函數(shù)我們稱為特征函數(shù)（characteristic function），或者從定義了集合的角度來將它稱為定義函數(shù)

6. 關(guān)系除法運(yùn)算

現(xiàn)在需要查詢囊括了表 Items 中所有商品的店鋪（仙臺(tái)店和東京店）。

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