SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS
(SELECT ＊
FROM Teams T2
WHERE T1.team_id = T2.team_id
AND status ＜＞’待命’);
2.1.1.1.1. “所有队员都处于待命状态”＝“不存在不处于待命状态的队员”
2.1.1.1.2. 查询性能很好
2.1.1.1.3. 结果中能体现出队员信息
2.1.1.2. -- 用集合表达全称量化命题(1)
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(＊) = SUM(CASE WHEN status =’待命’
THEN 1
ELSE 0 END);
2.1.1.2.1. 代码很简洁
2.1.1.2.2. 使用的是特征函数的方法
2.1.1.3. -- 用集合表达全称量化命题(2)
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) =’待命’
AND MIN(status) =’待命’;
2.1.1.3.1. 性能更好
2.1.1.3.1.1. 极值函数可以使用参数字段的索引
2.1.1.3.2. 如果元素最大值和最小值相等，那么这个集合中肯定只有一种值
2.1.1.4. -- 列表显示各个队伍是否所有队员都在待命
SELECT team_id,
CASE WHEN MAX(status) =’待命’AND MIN(status) =’待命’
THEN ’全都在待命’
ELSE’队长！人手不够’END AS status
FROM Teams
GROUP BY team_id;
2.1.1.4.1. 条件移到SELECT子句后，查询可能就不会被数据库优化了
3. 单重集合
3.1. 示例
3.1.1.
3.1.1.1. -- 选中材料存在重复的生产地
SELECT center
FROM Materials
GROUP BY center
HAVING COUNT(material) ＜＞ COUNT(DISTINCT material);
3.1.1.1.1.
SELECT center,
CASE WHEN COUNT(material) ＜＞ COUNT(DISTINCT material) THEN’存在重复’
ELSE’不存在重复’END AS status
FROM Materials
GROUP BY center;
3.1.1.2. --存在重复的集合：使用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);
3.2. 在数学中，通过GROUP BY生成的子集有一个对应的名字，叫作划分（partition）
3.2.1. 集合论和群论中的重要概念，指的是将某个集合按照某种规则进行分割后得到的子集
3.2.2. 这些子集相互之间没有重复的元素，而且它们的并集就是原来的集合
4. 寻找缺失的编号：升级版
4.1. 示例
4.1.1. -- 如果有查询结果，说明存在缺失的编号
SELECT’存在缺失的编号’AS gap
FROM SeqTbl
HAVING COUNT(＊) ＜＞ MAX(seq);
4.1.1.1. 有一个前提条件，即数列的起始值必须是1
4.1.2. -- 如果有查询结果，说明存在缺失的编号：只调查数列的连续性
SELECT’存在缺失的编号’ AS gap
FROM SeqTbl
HAVING COUNT(＊) ＜＞ MAX(seq) - MIN(seq) + 1 ;
4.1.3. -- 不论是否存在缺失的编号都返回一行结果
SELECT CASE WHEN COUNT(＊) = 0
THEN ’表为空’
WHEN COUNT(＊) ＜＞ MAX(seq) - MIN(seq) + 1
THEN ’存在缺失的编号’
ELSE’连续’END AS gap
FROM SeqTbl;
4.1.4. -- 查找最小的缺失编号：表中没有1时返回1
SELECT CASE WHEN COUNT(＊) = 0 OR MIN(seq) ＞ 1 -- 最小值不是1时→返回1
THEN 1
ELSE (SELECT MIN(seq +1) -- 最小值是1时→返回最小的缺失编号
FROM SeqTbl S1
WHERE NOT EXISTS
(SELECT ＊
FROM SeqTbl S2
WHERE S2.seq = S1.seq + 1)) END
FROM SeqTbl;
5. 为集合设置详细的条件
5.1. 示例
5.1.1.
5.1.1.1. 查询出75%以上的学生分数都在80分以上的班级
5.1.1.1.1.
SELECT class
FROM TestResults
GROUP BY class
HAVING COUNT(＊) ＊ 0.75
＜= SUM(CASE WHEN score ＞= 80
THEN 1
ELSE 0 END) ;
5.1.1.2. 查询出分数在50分以上的男生的人数比分数在50分以上的女生的人数多的班级
5.1.1.2.1.
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) ;
5.1.1.3. 查询出女生平均分比男生平均分高的班级
5.1.1.3.1. -- 比较男生和女生平均分的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) ;
5.1.1.3.2. -- 比较男生和女生平均分的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) ;
