SQL进阶系列之10HAVING子句又回来了

写在前面

HAVING子句的处理对象是集合而不是记录

各队，全队点名 --各队，全体点名！ CREATE TABLE Teams (member CHAR(12) NOT NULL PRIMARY KEY, team_id INTEGER NOT NULL, status CHAR(8) NOT NULL); INSERT INTO Teams VALUES('乔', 1, '待命'); INSERT INTO Teams VALUES('肯', 1, '出勤中'); INSERT INTO Teams VALUES('米克', 1, '待命'); INSERT INTO Teams VALUES('卡伦', 2, '出勤中'); INSERT INTO Teams VALUES('凯斯', 2, '休息'); INSERT INTO Teams VALUES('简', 3, '待命'); INSERT INTO Teams VALUES('哈特', 3, '待命'); INSERT INTO Teams VALUES('迪克', 3, '待命'); INSERT INTO Teams VALUES('贝斯', 4, '待命'); INSERT INTO Teams VALUES('阿伦', 5, '出勤中'); INSERT INTO Teams VALUES('罗伯特', 5, '休息'); INSERT INTO Teams VALUES('卡根', 5, '待命'); -- 找出全队全员处于待命状态的队伍 -- NOT EXISTS法 SELECT team_id,member FROM Teams AS T1 WHERE NOT EXISTS (SELECT * FROM Teams AS 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); -- 变通方法1 SELECT team_id FROM Teams GROUP BY team_id HAVING MAX(status) = '待命' AND MIN(status) ='待命'; -- 变通方法2 SELECT team_id, CASE WHEN MAX(status) = '待命' AND MIN(status) = '待命' THEN '全都在待命' ELSE '队长！人手不够' END AS status FROM Teams GROUP BY team_id; 单重集合与多重集合 --单重集合与多重集合 CREATE TABLE Materials (center CHAR(12) NOT NULL, receive_date DATE NOT NULL, material CHAR(12) NOT NULL, PRIMARY KEY(center, receive_date)); INSERT INTO Materials VALUES('东京' ,'2007-4-01', '锡'); INSERT INTO Materials VALUES('东京' ,'2007-4-12', '锌'); INSERT INTO Materials VALUES('东京' ,'2007-5-17', '铝'); INSERT INTO Materials VALUES('东京' ,'2007-5-20', '锌'); INSERT INTO Materials VALUES('大阪' ,'2007-4-20', '铜'); INSERT INTO Materials VALUES('大阪' ,'2007-4-22', '镍'); INSERT INTO Materials VALUES('大阪' ,'2007-4-29', '铅'); INSERT INTO Materials VALUES('名古屋', '2007-3-15', '钛'); INSERT INTO Materials VALUES('名古屋', '2007-4-01', '钢'); INSERT INTO Materials VALUES('名古屋', '2007-4-24', '钢'); INSERT INTO Materials VALUES('名古屋', '2007-5-02', '镁'); INSERT INTO Materials VALUES('名古屋', '2007-5-10', '钛'); INSERT INTO Materials VALUES('福冈' ,'2007-5-10', '锌'); INSERT INTO Materials VALUES('福冈' ,'2007-5-28', '锡'); -- 找出原料出现重复的产地 SELECT center FROM Materials GROUP BY center HAVING COUNT(*) <> COUNT(DISTINCT material); -- 另一种写法 SELECT center,CASE WHEN COUNT(*) <> COUNT(DISTINCT material) THEN '存在重复' ELSE '不存在重复' END AS status FROM Materials GROUP BY center; -- EXISTS写法 SELECT center,material FROM Materials AS M1 WHERE EXISTS (SELECT * FROM Materials AS M2 WHERE M1.center = M2.center AND M1.receive_date <> M2.receive_date AND M1.material = M2.material); 寻找缺失的编号：升级版 --寻找缺失的编号：升级版 CREATE TABLE SeqTbl ( seq INTEGER NOT NULL PRIMARY KEY); --不存在缺失编号（起始值＝1） DELETE FROM SeqTbl; INSERT INTO SeqTbl VALUES(1); INSERT INTO SeqTbl VALUES(2); INSERT INTO SeqTbl VALUES(3); INSERT INTO SeqTbl VALUES(4); INSERT INTO SeqTbl VALUES(5); -- 如果有查询结果，说明存在缺失的编号 SELECT '存在缺失的编号' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq) --只适用于从1开始的序列 -- 如果有查询结果，说明存在缺失的编号：只调查数列的连续性 SELECT '存在缺失的编号' AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1; -- 不论是否存在都返回一行结果 SELECT CASE WHEN COUNT(*) = 0 THEN '空表' WHEN COUNT(*) = MAX(seq) - MIN(seq) + 1 THEN '不存在缺失编号' ELSE '存在缺失编号' END AS gap FROM SeqTbl; -- 查找最小的缺失编号 SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1 ELSE (SELECT MIN(seq+1) FROM SeqTbl AS S1 WHERE NOT EXISTS (SELECT * FROM SeqTbl AS S2 WHERE S2.seq = S1.seq + 1)) END FROM SeqTbl; 为集合设置详细的条件 --为集合设置详细的条件 CREATE TABLE TestResults (student CHAR(12) NOT NULL PRIMARY KEY, class CHAR(1) NOT NULL, sex CHAR(1) NOT NULL, score INTEGER NOT NULL); INSERT INTO TestResults VALUES('001', 'A', '男', 100); INSERT INTO TestResults VALUES('002', 'A', '女', 100); INSERT INTO TestResults VALUES('003', 'A', '女', 49); INSERT INTO TestResults VALUES('004', 'A', '男', 30); INSERT INTO TestResults VALUES('005', 'B', '女', 100); INSERT INTO TestResults VALUES('006', 'B', '男', 92); INSERT INTO TestResults VALUES('007', 'B', '男', 80); INSERT INTO TestResults VALUES('008', 'B', '男', 80); INSERT INTO TestResults VALUES('009', 'B', '女', 10); INSERT INTO TestResults VALUES('010', 'C', '男', 92); INSERT INTO TestResults VALUES('011', 'C', '男', 80); INSERT INTO TestResults VALUES('012', 'C', '女', 21); INSERT INTO TestResults VALUES('013', 'D', '女', 100); INSERT INTO TestResults VALUES('014', 'D', '女', 0); INSERT INTO TestResults VALUES('015', 'D', '女', 0); -- 查出75%以上的学生分数都在80分以上的班级 SELECT class FROM TestResults GROUP BY class HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END); -- 查出分数在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); -- 比较男生和女生平均分的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); 小结各队，全队点名 --各队，全体点名！ CREA