【SQL进阶教程】HAVING子句又回来了

Posted on | In

整理一下在调查集合性质时经常用到的条件。这些条件可以在HAVING子句中使用，也可以通过SELECT子句在CASE表达式里使用。

nsYkDg.jpg

各队，全体点名

需要做的是查出现在可以出勤的队伍。可以出勤即队伍里所有队员都处于“待命”状态。使用的是下面这张表。

nrjzMd.jpg

1
2
3
4
5
6
7
-- 谓词
SELECT team_id, member
FROM Teams T1
WHERE NOT EXISTS (SELECT *
                  FROM Teams T2
                  WHERE T1.team_id = T2.team_id
                        AND status <> '待命');

nrvQoV.png

这个查询性能很好，而且结果中能体现出队员信息，这些是它好的地方。但是因为使用了双重否定，所以理解起来不是很容易。而如果使用HAVING子句，写起来就非常简单了，像下面这样。

1
2
3
4
5
-- 集合（1）
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待命' THEN 1 ELSE 0 END);

nrvWwt.png

顺便说一下，HAVING子句中的条件还可以像下面这样写。

1
2
3
4
5
6
-- 集合（2）
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING MIN(status) = '待命'
       AND MAX(status) = '待命';

极值函数可以使用参数字段的索引，所以这种写法性能更好。

当然也可以把条件放在SELECT子句里，以列表形式显示出各个队伍是否所有队员都在待命，这样的结果更加一目了然。

1
2
3
4
5
SELECT team_id,
       CASE WHEN MIN(status) = '待命' AND MAX(status) = '待命' THEN '全部都在待命'
            ELSE '队长！人手不够' END AS status
FROM Teams
GROUP BY team_id;

nsSki6.png

需要注意的是，条件移到SELECT子句后，查询可能就不会被数据库优化了，所以性能上相比HAVING子句的写法会差一些。

单重集合与多重集合

例如，有下面这样一张管理各个生产地的材料库存的表。

nsFvkj.jpg

需要调查出存在重复材料的生产地。

1
2
3
4
SELECT center
FROM Materials
GROUP BY center
HAVING COUNT(material) <> COUNT(DISTINCT material);

nskbCR.png

和前面一样，可以把条件移到SELECT子句中，这样就能在结果中清晰地看到各个生产地是否存在重复材料了。

1
2
3
4
5
SELECT center,
       CASE WHEN COUNT(material) <> COUNT(DISTINCT material) THEN '存在重复'
            ELSE '不存在重复' END AS status
FROM Materials
GROUP BY center;

nsA0R1.png

顺便说一下，这个问题也可以通过将HAVING改写成EXISTS的方式来解决。

1
2
3
4
5
6
7
SELECT center, material
FROM Materials M1
WHERE EXISTS (SELECT *
              FROM Materials M2
              WHERE M1.center = M2.center
                    AND M1.material = M2.material
                    AND M1.reveive_date <> M2.receive_date);

nsE1FH.png

用EXISTS改写后的SQL语句也能够查出重复的具体是哪一种材料，而且使用EXISTS后性能也很好。

寻找缺失的编号：升级版

下面这样一条查询数列的缺失编号的查询语句。

1
2
3
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

这条SQL语句有一个前提条件，即数列的起始值必须是1.思考一下不管数列的最小值是多少，都能用来判断该数列是否连续的SQL语句。

nsKoV0.jpg

如果数列的最小值和最大值之间没有缺失的编号，它们之间包含的元素的个数应该是“最大值-最小值+1”。因此，像下面这样写比较条件就可以了。

1
2
3
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1;

如果不论是否存在缺失的编号，都想要返回结果，那么只需要像下面这样把条件写到SELECT里就可以了。

1
2
3
4
5
SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
            WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1 THEN '存在缺失的编号'
            ELSE '连续'
       END AS gap
FROM SeqTbl;

接下来也顺便改进一下查找最小的缺失编号的SQL语句，去掉起始值必须是1的限制。对于像这样表中原本就不存在1的情况，追加一个条件分支让它返回1，即像下面这样来写SQL语句。

1
2
3
4
5
6
7
8
SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1
            ELSE (SELECT MIN(seq + 1)
                  FROM SeqTbl S1
                  WHERE NOT EXISTS (SELECT *
                                    FROM SeqTbl S2
                                    WHERE S2.seq = S1.seq + 1))
       END
FROM SeqTbl;

NOT IN改写成了NOT EXISTS，这样写是为了处理值为NULL的情况，以及略微优化一下性能。特别是如果在“seq”列上建立了索引，那么使用NOT EXISTS能明显改善性能。

为集合设置详细的条件

这里以下面这张记录了学生考试成绩的表为例。

nslbPf.jpg

  1. 查询出75%以上的学生分数在80分以上的班级
1
2
3
4
SELECT class
FROM TestResults
GROUP BY class
HAVING COUNT(*) * 0.75 <= SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END);

ns8nC6.png

  1. 查询出分数在50分以上的男生的人数比分数在50分以上的女生的人数多的班级
1
2
3
4
5
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);

nsGZLQ.png

  1. 查询出女生平均分比男生平均分高的班级

从表中的数据可以发现，D班全是女生，男生的平均分无法计算。合理的处理方法是，保证对空集求平均的结果是“未定义”。

1
2
3
4
5
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);

nsJbjO.png