HAVING子句的力量

• 表不是文件，记录也没有顺序，所以SQL不进行排序
• SQL不是面向过程语言，而是面向集合语言

寻找缺失的编号

在以前的SQL标准里，HAVING子句必须和GROUP BY子句一起使用，但是，按照现在的SQL标准来说，HAVING子句是可以单独使用的。不过这种情况下，就不能在SELECT子句里引用原来的表里的列了，要么使用常量，要么就得像SELECT COUNT(*)这样使用聚合函数

-- 如果有查询结果，说明存在缺失的编号
SELECT '存在缺失的编号'
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

-- 查询编号的最小值
SELECT MIN(seq+1) AS gap
FROM SeqTbl
WHERE (seq + 1) NOT IN (SELECT seq FROM SeqTbl);
-- 如果表SeqTbl里包含NULL，那么上述SQL语句的查询结果就是错误的
-- 除此之外，如果表SeqTbl表中缺失编号的最小值为1，上述SQL语句无法得出正确的结果

用HAVING子句进行子查询：求众数

-- 使用谓词求众数
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= (SELECT COUNT(*) 
                   FROM Graduates
                   GROUP BY income);

* 使用HAVING子句进行自连接：求中位数

-- 在HAVING子句中使用非等值连接
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
     FROM Graduates T1, Graduates T2
     GROUP BY T1.income
-- S1的条件
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
-- S2的条件
AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2) TMP;

查询不包含NULL的集合

COUNT函数的使用方法有COUNT(*)和COUNT(列名)两种，它们的区别有两个：第一个是性能上的区别；第二个是COUNT(*)可以用于NULL，而COUNT(列名)与其他聚合函数一样，要先排除掉NULL的行再进行统计

-- 使用COUNT函数查询“提交日期”列内不包括NULL的学院
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

-- 使用CASE表达式查询“提交日期”列内不包括NULL的学院
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

用关系除法运算进行购物篮分析

Items表

ShopItems表

-- 精确关系除法运算：使用外连接和COUNT函数
SELECT SI.shop
FROM ShopItems SI LEFT JOIN Items I
ON SI.item = I.item
GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items) -- 条件1
AND COUNT(I.item) = (SELECT COUNT(item) FROM Items) -- 条件2

选出队员可以全部出勤的队伍

-- 用集合表达全称量化命题
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命'
                     THEN 1 ELSE 0 END);
                     
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MAX(status) = '待命'
AND MIN(status) = '待命';

为集合设置详细的条件

-- 查询出75%以上的学生分数都在80分以上的班级
SELECT class 
FROM TestResults
GROUP BY class
HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80
                             THEN 1
                             ELSE 0 END);
                             
-- 查询出分数在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);

-- 查询出女生平均分比男生平均分高的班级
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);
            
-- 注意：对空集求平均值后返回NULL