解读SQL中GROUP BY和HAVING子句中使用NULL条件问题

阿周 · 发表于 6 天前

SQL中GROUP BY和HAVING子句中使用NULL条件

SQL ：

SELECT COUNT(1), tid, sap_do, batch, skucode,batch
FROM lt
WHERE IFNULL(sap_do, '') != ''
AND created_time >= 1722441600
GROUP BY tid, sap_do, skucode
HAVING COUNT(1) > 1
AND batch IS NULL;

复制代码

查询结果为空；实际数据库存在batch IS NULL的

修改后：

SELECT COUNT(1), tid, sap_do, skucode, batch
FROM lt
WHERE IFNULL(sap_do, '') != ''
AND created_time >= 1722441600
GROUP BY tid, sap_do, skucode
HAVING COUNT(1) > 1
AND SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) > 0;

复制代码

问题的核心在于 GROUP BY 和 HAVING 子句的工作方式，以及 NULL 的处理规则

1. HAVING batch IS NULL 没有数据的原因

当你使用 GROUP BY 并在 HAVING 子句中直接使用 batch IS NULL，SQL 会在分组后的结果中查找 batch 的值是否为 NULL。但是由于 GROUP BY 会对所有同一组内的数据进行聚合，batch 字段可能包含 NULL 和非 NULL 的混合值。
对于 GROUP BY 而言，它不会简单地保留 batch 字段的某一个特定值，因此如果某个分组内的 batch 值既有 NULL 又有非 NULL 的情况，batch IS NULL 这个条件会失效，因为 SQL 无法确定这一组的 batch 值究竟是 NULL 还是非 NULL。
直接使用 HAVING batch IS NULL 通常只能在整个分组的 batch 全部是 NULL 的情况下才会生效。
简而言之，当 batch 列在分组后不全是 NULL 时，HAVING batch IS NULL 无法筛选出结果。

2. HAVING SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) > 0 结果正确的原因

SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) 的逻辑是通过 CASE 语句来计算某个分组内 batch 列为 NULL 的记录数。
SUM 是对整个分组内的每一条记录进行处理，因此即使该组内存在 batch 的非 NULL 值，只要有任何一条记录的 batch 是 NULL，SUM 就能正确计算出这一组包含 NULL 值的数量。
解释：

SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) > 0：通过这个 SUM，我们计算出当前分组内有多少条记录的 batch 为 NULL，如果结果大于 0，说明这一组内存在 NULL 值的 batch。
HAVING batch IS NULL：只能筛选出分组后 batch 的值完全为 NULL 的情况，无法处理那些包含 NULL 和非 NULL 值的分组。

总结

HAVING batch IS NULL 只能处理整个分组中的 batch 全为 NULL 的情况，这在有混合值的分组中会导致结果为空。
HAVING SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) > 0 可以处理分组中包含 NULL 和非 NULL 的情况，因此它能正确返回包含 NULL 值的分组。
这种 SUM(CASE WHEN…) 逻辑提供了更细粒度的控制，可以确保即使在分组中存在多个不同的 batch 值，依然可以正确识别出那些包含 NULL 的分组。

补充

如果查询id字段的话上面的SQL结果会发现，id结果不是我们想要的batch为null的行ID
可改造SQL为下面执行：

SELECT id, tid, sap_do, skucode, batch
FROM lt
WHERE batch IS NULL
AND (tid, sap_do, skucode) IN (
SELECT tid, sap_do, skucode
FROM lt
WHERE IFNULL(sap_do, '') != ''
AND created_time >= 1725120000
GROUP BY tid, sap_do, skucode
HAVING COUNT(1) > 1
AND SUM(CASE WHEN batch IS NULL THEN 1 ELSE 0 END) > 0
);

复制代码

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/328891uv5.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！