?SQL 是一種基于“面向集合”思想設(shè)計(jì)的語(yǔ)言。HAVING 子句是一個(gè)聚合函數(shù)，用于過(guò)濾分組結(jié)果。

1 實(shí)踐

1.1 缺失的編號(hào)

圖 連續(xù)編號(hào)記錄表t_seq_record

需求：判斷seq 列編號(hào)是否有缺失。

SELECT '存在缺失的編號(hào)' AS res
FROM t_seq_record 
HAVING COUNT(*) != MAX(seq);

需求：查詢?nèi)笔Ь幪?hào)的最小值。

SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1 
ELSE MIN(seq) + 1 END AS minSeq 
FROM t_seq_record
WHERE seq + 1 NOT IN (SELECT seq FROM t_seq_record WHERE seq IS NOT NULL);

需求：存在缺失的編號(hào)時(shí)返回“存在缺失的編號(hào)”，不存在缺失的編號(hào)時(shí)返回“不存在缺失的編號(hào)”。

SELECT 
CASE WHEN MAX(seq) != COUNt(*) THEN '存在缺失的編號(hào)'
ELSE '不存在缺失的編號(hào)' END as res 
FROM t_seq_record;

1.2 求眾數(shù)

圖 工資記錄t_sales_record 表

需求：查詢出工資眾數(shù)。

SELECT income
FROM t_sales_record
GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(`count`) FROM (SELECT COUNT(*) AS `count` FROM t_sales_record GROUP BY income)temp);
-- 全稱量化 ALL
SELECT income
FROM t_sales_record
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*) AS `count` FROM t_sales_record GROUP BY income);

1.3 查詢不包含NULL的集合

圖 學(xué)生報(bào)告提交記錄t_student_record 表

需求：查詢哪些學(xué)院的學(xué)生全部都提交了報(bào)告。

-- EXISTS 
SELECT *
FROM t_student_submit_record s1
WHERE NOT EXISTS 
(SELECT *FROM t_student_submit_record s2 WHERE s2.dpt = s1.dpt AND s2.submit_date IS NULL 
);
-- HAVING + COUNT
SELECT dpt
FROM t_student_submit_record s1
GROUP BY dpt 
HAVING COUNT(*) = COUNT(submit_date)
-- CASE 
SELECT dpt,
CASE WHEN COUNT(*) = COUNT(submit_date) THEN '全部提交'
ELSE '存在未提交的' END AS '提交狀態(tài)'
FROM t_student_submit_record s1
GROUP BY dpt; 
-- CASE2 
SELECT dpt
FROM t_student_submit_record
GROUP BY dpt 
HAVING COUNT(*) = SUM(CASE WHEN submit_date IS NULL THEN 0 ELSE 1 END);

需求：查詢“全體學(xué)生都在9月份提交了報(bào)告的學(xué)院”

SELECT dpt
FROM t_student_submit_record 
GROUP BY dpt 
HAVING COUNT(*) = SUM( CASE WHEN MONTH(submit_date) = 9 THEN 1 ELSE 0 END);
-- EXISTS
SELECT *
FROM t_student_submit_record s1 
WHERE NOT EXISTS 
(SELECT *FROM t_student_submit_record s2 WHERE s2.dpt = s1.dpt AND (s2.submit_date IS NULL OR MONTH(s2.submit_date) != 9)
);
-- EXTRACT
SELECT dpt
FROM t_student_submit_record
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT(MONTH FROM submit_date) = 9 THEN 1 ELSE 0 END);

1.4 特征函數(shù)

圖 學(xué)生成績(jī)記錄t_student_score表

需求：查詢出75% 以上的學(xué)生分?jǐn)?shù)都在80分以上的班級(jí)。

SELECT `class`
FROM t_student_score
GROUP BY `class`
HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END);

需求：查詢出分?jǐn)?shù)在50分以上的男士的人數(shù)比分?jǐn)?shù)在50分以上的女生的人數(shù)多的班級(jí)。

SELECT `class`
FROM t_student_score
GROUP BY `class`
HAVING SUM(CASE WHEN sex = '男' AND score >= 50 THEN 1 ELSE 0 END) > SUM(CASE WHEN sex = '女' AND score >= 50 THEN 1 ELSE 0 END);

需求：查詢出女生平均分比男士平均分高的班級(jí)。

SELECT `class`
FROM t_student_score
GROUP BY `class`
HAVING AVG(CASE WHEN sex = '男' THEN score ELSE NULL END) < AVG(CASE WHEN sex = '女' THEN score ELSE NULL END);

1.5 使用HAVING 表達(dá)全稱量化

表 團(tuán)隊(duì)成員狀態(tài)t_team_member 表

需求：查找出可以出勤的隊(duì)伍（隊(duì)伍里所有隊(duì)員都處于“待命”狀態(tài)）。

-- EXISTS
SELECT * 
FROM t_team_member t1
WHERE NOT EXISTS 
(SELECT *FROM t_team_member t2WHERE t2.team_id = t1.team_id AND t2.`status` != '待命'
);
-- HAVING
SELECT team_id 
FROM t_team_member t1
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN `status` = '待命' THEN 1 ELSE 0 END);
-- HAVING + ALL
SELECT team_id 
FROM t_team_member t1
GROUP BY team_id
HAVING '待命' = ALL (SELECT `status` FROM t_team_member WHERE team_id = t1.team_id);
-- HAVING + ALL
SELECT team_id 
FROM t_team_member t1
GROUP BY team_id
HAVING MIN(`status`) = '待命' AND MAX(`status`) = '待命';

1.6 單重集合與多重集合

關(guān)系數(shù)據(jù)庫(kù)的集合是允許數(shù)據(jù)重復(fù)的多重集合。與之相反，通常意義的集合論中的集合不允許數(shù)據(jù)重復(fù)。

圖 材料庫(kù)存記錄t_material_stock 表

需求： 選中材料中存在重復(fù)的生產(chǎn)地。

-- EXISTS
SELECT *
FROM t_material_stock m1 
WHERE EXISTS 
(SELECT *FROM t_material_stock m2 WHERE m2.center = m1.center AND m2.receive_date != m1.receive_date AND m2.material = m1.material
);
-- HAVING 
SELECT center 
FROM t_material_stock 
GROUP BY center
HAVING COUNT(*) != COUNT(DISTINCT material);

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

圖 商品項(xiàng)t_goods_item表與店鋪商品信息t_shop_items 表

需求：查詢囊括了t_goods_item 表所有商品的店鋪。

-- 左連接 
SELECT s.shop,COUNT(g.item) 
FROM t_shop_items s 
LEFT JOIN t_goods_item g ON s.item = g.item
GROUP BY s.shop 
HAVING COUNT(g.item) = (SELECT COUNT(*) FROM t_goods_item);

需求：查詢店鋪囊括了t_goods_item表所有商品且不包含其他商品的店鋪。

SELECT s.shop,COUNT(g.item) 
FROM t_shop_items s 
LEFT JOIN t_goods_item g ON s.item = g.item
GROUP BY s.shop 
HAVING COUNT(g.item) = (SELECT COUNT(*) FROM t_goods_item)
AND COUNT(s.item) = (SELECT COUNT(*) FROM t_goods_item);

需求：查詢商品現(xiàn)有庫(kù)存的商品種類數(shù)，不足的商品種類數(shù)。

SELECT s.shop,COUNT(s.item) AS itemCnt,((SELECT COUNT(*) FROM t_goods_item) - COUNT(g.item)) AS diffCnt
FROM t_shop_items s 
LEFT JOIN t_goods_item g ON s.item = g.item
GROUP BY s.shop; 

1.8 HAVING 子句和窗口函數(shù)

需求：用窗口函數(shù)實(shí)現(xiàn)1.3的需求。

-- 窗口函數(shù)
SELECT DISTINCT dpt
FROM (SELECT *,COUNT(*) OVER (PARTITION BY dpt) AS cnt_all,COUNT(submit_date) OVER (PARTITION BY dpt) AS submit_allFROM t_student_submit_record
) temp 
WHERE cnt_all = submit_all;