SQL 中 HAVING 常见的使用方法

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

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

-- 求众数的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 ) ;

-- 求中位数的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;

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

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

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)

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