SQL HAVING子句

 SQL 是一种基于“面向集合”思想设计的语言。HAVING 子句是一个聚合函数，用于过滤分组结果。

1 实践

1.1 缺失的编号

图 连续编号记录表t_seq_record

需求：判断seq 列编号是否有缺失。

SELECT '存在缺失的编号' AS res
FROM t_seq_record 
HAVING COUNT(*) != MAX(seq);

需求：查询缺失编号的最小值。

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

需求：存在缺失的编号时返回“存在缺失的编号”，不存在缺失的编号时返回“不存在缺失的编号”。

SELECT 
CASE WHEN MAX(seq) != COUNt(*) THEN '存在缺失的编号'
ELSE '不存在缺失的编号' END as res 
FROM t_seq_record;

1.2 求众数

图 工资记录t_sales_record 表

需求：查询出工资众数。

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的集合

图 学生报告提交记录t_student_record 表

需求：查询哪些学院的学生全部都提交了报告。

-- 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 '提交状态'
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);

需求：查询“全体学生都在9月份提交了报告的学院”

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 特征函数

图 学生成绩记录t_student_score表

需求：查询出75% 以上的学生分数都在80分以上的班级。

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

需求：查询出分数在50分以上的男士的人数比分数在50分以上的女生的人数多的班级。

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

需求：查询出女生平均分比男士平均分高的班级。

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 表达全称量化

表 团队成员状态t_team_member 表

需求：查找出可以出勤的队伍（队伍里所有队员都处于“待命”状态）。

-- 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 单重集合与多重集合

关系数据库的集合是允许数据重复的多重集合。与之相反，通常意义的集合论中的集合不允许数据重复。

图 材料库存记录t_material_stock 表

需求： 选中材料中存在重复的生产地。

-- 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 关系除法运算

图 商品项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);

需求：查询商品现有库存的商品种类数，不足的商品种类数。

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 子句和窗口函数

需求：用窗口函数实现1.3的需求。

-- 窗口函数
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;