如何在SQL子查询中忽略NULL或0值，排除全为0的类别行？

解决方案 #

方案1：用CTE预计算后过滤

先通过公共表表达式（CTE）一次性算出每个类别的各货币合计，再在外部查询中排除所有合计为0的行，避免重复编写子查询：

WITH CategoryTotals AS (
    SELECT 
        S.[Category],
        ISNULL(SUM(CASE WHEN [Currency] = 'USD' THEN [Amount] END), 0) AS Total_USD,
        ISNULL(SUM(CASE WHEN [Currency] = 'ERU' THEN [Amount] END), 0) AS Total_ERU,
        ISNULL(SUM(CASE WHEN [Currency] = 'SAR' THEN [Amount] END), 0) AS Total_SAR
    FROM Sale AS S
    GROUP BY S.[CategoryID], S.[Category], S.[CategoryName] -- 注意分组字段需与表结构匹配
)
SELECT Category, Total_USD, Total_ERU, Total_SAR
FROM CategoryTotals
WHERE Total_USD + Total_ERU + Total_SAR <> 0;

方案2：条件聚合+Having过滤

把原有的子查询替换为条件聚合（CASE WHEN），只需扫描一次表，性能更优，同时Having子句直接使用聚合结果，无需重复逻辑：

SELECT 
    S.[Category],
    ISNULL(SUM(CASE WHEN [Currency] = 'USD' THEN [Amount] END), 0) AS Total_USD,
    ISNULL(SUM(CASE WHEN [Currency] = 'ERU' THEN [Amount] END), 0) AS Total_ERU,
    ISNULL(SUM(CASE WHEN [Currency] = 'SAR' THEN [Amount] END), 0) AS Total_SAR
FROM Sale AS S
GROUP BY S.[CategoryID], S.[Category], S.[CategoryName]
HAVING ISNULL(SUM(CASE WHEN [Currency] = 'USD' THEN [Amount] END), 0) + 
       ISNULL(SUM(CASE WHEN [Currency] = 'ERU' THEN [Amount] END), 0) + 
       ISNULL(SUM(CASE WHEN [Currency] = 'SAR' THEN [Amount] END), 0) <> 0;

注意事项 #

• 条件聚合比原多子查询方案效率更高，因为只需要遍历一次表；
• 如果Amount不会出现负数，可简化Having条件为SUM([Amount]) <> 0，但如果存在正负抵消合计为0的情况，需保留多字段求和判断；
• 原SQL中的CategoryID和CategoryName在表描述中未提及，需确保这些字段真实存在，否则调整分组字段为Category即可。

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