HAVING子句的力量

• 表不是檔案，記錄也沒有順序，所以SQL不進行排序
• SQL不是程式導向語言，而是面向集合語言

尋找缺失的編號

在以前的SQL標準裡，HAVING子句必須和GROUP BY子句一起使用，但是，按照現在的SQL標準來說，HAVING子句是可以單獨使用的。不過這種情況下，就不能在SELECT子句裡引用原來的表裡的列了，要麼使用常量，要麼就得像SELECT COUNT(*)這樣使用聚合函式

-- 如果有查詢結果，說明存在缺失的編號
SELECT '存在缺失的編號'
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

-- 查詢編號的最小值
SELECT MIN(seq+1) AS gap
FROM SeqTbl
WHERE (seq + 1) NOT IN (SELECT seq FROM SeqTbl);
-- 如果表SeqTbl裡包含NULL，那麼上述SQL語句的查詢結果就是錯誤的
-- 除此之外，如果表SeqTbl表中缺失編號的最小值為1，上述SQL語句無法得出正確的結果

用HAVING子句進行子查詢：求眾數

-- 使用謂詞求眾數
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= (SELECT COUNT(*) 
                   FROM Graduates
                   GROUP BY income);

* 使用HAVING子句進行自連線：求中位數

-- 在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;

查詢不包含NULL的集合

COUNT函式的使用方法有COUNT(*)和COUNT(列名)兩種，它們的區別有兩個：第一個是效能上的區別；第二個是COUNT(*)可以用於NULL，而COUNT(列名)與其他聚合函式一樣，要先排除掉NULL的行再進行統計

-- 使用COUNT函式查詢“提交日期”列內不包括NULL的學院
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

-- 使用CASE表示式查詢“提交日期”列內不包括NULL的學院
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

用關係除法運算進行購物籃分析

Items表

ShopItems表

-- 精確關係除法運算：使用外連線和COUNT函式
SELECT SI.shop
FROM ShopItems SI LEFT 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

選出隊員可以全部出勤的隊伍

-- 用集合表達全稱量化命題
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命'
                     THEN 1 ELSE 0 END);
                     
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待命'
AND MIN(status) = '待命';

為集合設定詳細的條件

-- 查詢出75%以上的學生分數都在80分以上的班級
SELECT class 
FROM TestResults
GROUP BY class
HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80
                             THEN 1
                             ELSE 0 END);
                             
-- 查詢出分數在50分以上的男生的人數比分數在50分以上的女生的人數多的班級
SELECT class
FROM TestResults
GROUP BY class
HAVING SUM(CASE WHEN score >= 50 AND sex = '男'
          THEN 1 
          ELSE 0 END)
       > SUM(CASE WHEN score >= 50 AND sex = '女'
            THEN 1 
            ELSE 0 END);

-- 查詢出女生平均分比男生平均分高的班級
SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男'
          THEN score
          ELSE NULL END)
       < AVG(CASE WHEN sex = '女'
            THEN score
            ELSE NULL END);
            
-- 注意：對空集求平均值後返回NULL