制作个人网站的要求,网站用户细分,网页建站建设教程,小程序变成网页链接SQL 是一种基于“面向集合”思想设计的语言。HAVING 子句是一个聚合函数#xff0c;用于过滤分组结果。
1 实践
1.1 缺失的编号 图 连续编号记录表t_seq_record
需求#xff1a;判断seq 列编号是否有缺失。
SELECT 存在缺失的编号 AS res
FROM t_seq_record
HAVING COUN… 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;
