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;