T-SQL查询结果排除NULL值：原因分析与实现方案

咱们一步步拆解问题：

• CASE语句的逻辑缺陷
  你当前的写法是CASE WHEN 条件 THEN SUM(...) END——只有当分组内的所有行都满足CASE的判断条件时，才会计算SUM的结果；如果分组里没有符合条件的行，或者条件不成立，CASE会默认返回NULL。比如某个分组的行都是m1.is_internal=1 AND m2.is_internal=0，那internal_volume和external_volume_in都会变成NULL。

• LEFT JOIN引入的NULL值
  你用了LEFT JOIN关联messages_addresses表，这意味着即使msg.originator或msg.recipient在地址表里找不到匹配项，msg的行依然会被保留。此时m1.is_internal或m2.is_internal会是NULL，导致两个CASE的条件都不成立，最终整行的两个列都是NULL。

根据你的需求，这里提供几种针对性的调整方案：

方案一：修正CASE与SUM的嵌套逻辑，避免列值为NULL #

把SUM包裹CASE的写法反过来，让CASE作为SUM的计算项，不满足条件时返回0，这样SUM始终会有数值结果：

SELECT 
    SUM(CASE WHEN (m1.is_internal = 1 AND m2.is_internal = 1) THEN CAST([size] AS BIGINT) ELSE 0 END) AS internal_volume,
    SUM(CASE WHEN (m1.is_internal = 0 AND m2.is_internal = 1) THEN CAST([size] AS BIGINT) ELSE 0 END) AS external_volume_in
FROM messagesgal msg
LEFT JOIN messages_addresses m1 ON msg.originator = m1.address
LEFT JOIN messages_addresses m2 ON msg.recipient = m2.address
WHERE date >= 43179
GROUP BY floor(date), m1.is_internal, m2.is_internal

方案二：直接过滤掉全NULL的分组行 #

如果希望彻底移除两个列都是NULL的行，可以在查询末尾添加HAVING子句：

SELECT 
    CASE WHEN (m1.is_internal = 1 AND m2.is_internal = 1) THEN SUM(CAST([size] AS BIGINT)) END AS internal_volume,
    CASE WHEN (m1.is_internal = 0 AND m2.is_internal = 1) THEN SUM(CAST([size] AS BIGINT)) END AS external_volume_in
FROM messagesgal msg
LEFT JOIN messages_addresses m1 ON msg.originator = m1.address
LEFT JOIN messages_addresses m2 ON msg.recipient = m2.address
WHERE date >= 43179
GROUP BY floor(date), m1.is_internal, m2.is_internal
HAVING internal_volume IS NOT NULL OR external_volume_in IS NOT NULL

方案三：从根源减少NULL（仅适用于需完整匹配地址的场景） #

如果你的业务逻辑要求originator和recipient必须在地址表里存在匹配项，可以把LEFT JOIN改成INNER JOIN，这样只会保留有完整地址匹配的行，从根源上避免因关联失败产生的NULL：

SELECT 
    SUM(CASE WHEN (m1.is_internal = 1 AND m2.is_internal = 1) THEN CAST([size] AS BIGINT) ELSE 0 END) AS internal_volume,
    SUM(CASE WHEN (m1.is_internal = 0 AND m2.is_internal = 1) THEN CAST([size] AS BIGINT) ELSE 0 END) AS external_volume_in
FROM messagesgal msg
INNER JOIN messages_addresses m1 ON msg.originator = m1.address
INNER JOIN messages_addresses m2 ON msg.recipient = m2.address
WHERE date >= 43179
GROUP BY floor(date), m1.is_internal, m2.is_internal

你可以根据实际业务需求选择单独使用某一种方案，或者结合多个方案（比如方案一+方案二）来达到最理想的效果。

内容的提问来源于stack exchange，提问作者alexithymia