1-10 HAVING 子句又回来了

前言

学习 SQL 时最大的阻碍就是我们已经习惯了的面向过程语言的思考方式（排序、循环、条件分支、赋值等）。为了深入理解 SQL 的本质，我们必须暂时摒弃已有观念

学习 HAVING 子句的用法是帮助我们顺利地忘掉面向过程语言的思考方式并理解 SQL面向集合特性的最为有效的方法。这是因为，HAVING 子句的处理对象是集合而不是记录，所以只有习惯了面向集合的思考方式，才能真正地学好它

各队，全体点名

查出现在可以出勤的队伍。可以出勤即队伍里所有队员都处于“待命”状态。即3队和4队

-- “所有队员都处于‘待命’状态”这个条件是全称量化命题，所以可以用 NOT EXISTS 来表达。
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS
(SELECT * FROM Teams T2
WHERE T1.team_id = T2.team_id
AND status <> '待命' );

-- 等价的having形式, 便于理解
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = 
SUM(CASE WHEN status = '待命'THEN 1 ELSE 0 END);

-- 骚操作
SELECT team_id,
CASE WHEN MAX(status) = '待命' AND MIN(status) = '待命'
THEN '全都在待命'
ELSE '队长！人手不够' END AS status
FROM Teams
GROUP BY team_id;

单重集合与多重集合

调查出存在重复材料的生产地。结果集合是锌重复的东京，以及钛和钢重复的名古屋。

-- 选中材料存在重复的生产地
SELECT center
FROM Materials
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

-- case when 版本
SELECT center,
CASE WHEN COUNT(material) <> COUNT(DISTINCT material) THEN '存在重复'
ELSE '不存在重复' END AS status
FROM Materials
GROUP BY center;

-- 存在重复的集合：使用EXISTS
SELECT center, material
FROM Materials M1
WHERE EXISTS
(SELECT *
FROM Materials M2
WHERE M1.center = M2.center
AND M1.receive_date <> M2.receive_date
AND M1.material = M2.material);c

为集合设置详细的条件

第 1 题：请查询出 75% 以上的学生分数都在 80 分以上的班级。
设每个班80分以上的学生人数为X, 则X/总数> 0.75, 那么0.75*总数<X

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

结果

第 2 题：请查询出分数在 50 分以上的男生的人数比分数在 50 分以上的女生的人数多的班级。

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

结果

第 3 题：请查询出女生平均分比男生平均分高的班级。

-- 比较男生和女生平均分的SQL 语句(1)：对空集使用AVG 后返回0
SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男' THEN score ELSE 0 END)
< AVG(CASE WHEN sex = '女' THEN score ELSE 0 END) ;

-- 比较男生和女生平均分的SQL 语句(2)：对空集求平均值后返回NULL
SELECT class
FROM TestResults
GROUP BY class
HAVING AVG(CASE WHEN sex = '男' THEN score ELSE NULL END)
< AVG(CASE WHEN sex = '女' THEN score ELSE NULL END) ;

语句(1)结果

语句(2)结果

本节要点

1. 在 SQL 中指定搜索条件时，最重要的是搞清楚搜索的实体是集合还是集合的元素。
2. 如果一个实体对应着一行数据→ 那么就是元素，所以使用WHERE 子句。
3. 如果一个实体对应着多行数据→ 那么就是集合，所以使用HAVING 子句。
4. HAVING 子句可以通过聚合函数（特别是极值函数）针对集合指定各种条件。
5. 如果通过 CASE 表达式生成特征函数，那么无论多么复杂的条件都可以描述。