Teradata SQL分组查询：无对应记录时为求和字段赋值0的方法

我用以下查询统计金额列求和，但当CUST_TYPE为Corporates且FINAL_SME_CATEGORY为NATURAL PERSON时，因表中无对应记录，查询结果直接缺失该行。我需要让该行显示出来并为TOTAL_SUM赋值0，尝试过ZEROIFNULL、NVL、COALESCE及CASE语句，均未得到预期结果。

原始查询：

SELECT CUST_TYPE,FINAL_SME_CATEGORY, SUM(CUST_COMPENSATABLE_AMT) AS TOTAL_SUM 
FROM  ddewd10s.FSCS_LIMIT_UTIL_SCV 
WHERE FINAL_SME_CATEGORY IN ('SMALL','NATURAL PERSON') 
GROUP BY 1,2 
ORDER BY 1,2;

尝试过的无效查询：

-- COALESCE 版本
SELECT CUST_TYPE,FINAL_SME_CATEGORY, COALESCE(SUM(CUST_COMPENSATABLE_AMT), 0) AS TOTAL_SUM 
FROM  ddewd10s.FSCS_LIMIT_UTIL_SCV 
WHERE FINAL_SME_CATEGORY IN ('SMALL','NATURAL PERSON') 
GROUP BY 1,2 
ORDER BY 1,2;

-- ZEROIFNULL 版本
SELECT CUST_TYPE,FINAL_SME_CATEGORY, ZEROIFNULL(SUM(CUST_COMPENSATABLE_AMT)) AS TOTAL_SUM 
FROM  ddewd10s.FSCS_LIMIT_UTIL_SCV 
WHERE FINAL_SME_CATEGORY IN ('SMALL','NATURAL PERSON') 
GROUP BY 1,2 
ORDER BY 1,2;

-- NVL 版本
SELECT CUST_TYPE,FINAL_SME_CATEGORY, NVL(SUM(CUST_COMPENSATABLE_AMT),0) AS TOTAL_SUM 
FROM  ddewd10s.FSCS_LIMIT_UTIL_SCV 
WHERE FINAL_SME_CATEGORY IN ('SMALL','NATURAL PERSON') 
GROUP BY 1,2 
ORDER BY 1,2;

-- CASE 语句版本
SELECT CUST_TYPE, FINAL_SME_CATEGORY, CASE WHEN SUM(CUST_COMPENSATABLE_AMT)=0 THEN 0 ELSE SUM(CUST_COMPENSATABLE_AMT) END AS TOTAL_SUM 
FROM  ddewd10s.FSCS_LIMIT_UTIL_SCV 
WHERE FINAL_SME_CATEGORY IN ('SMALL','NATURAL PERSON') 
GROUP BY 1,2 
ORDER BY 1,2;

之前的方法无效，核心原因是：当某组完全没有匹配记录时，GROUP BY不会生成该行，所以COALESCE这类函数根本没有处理对象——连行都不存在，自然无法赋值0。

要解决这个问题，需要先构造出所有需要的CUST_TYPE和FINAL_SME_CATEGORY组合，再与原表做左连接，确保所有组合行都保留，最后对求和结果做非空转换。

方法一：手动构造固定组合（适合值明确的场景） #

如果CUST_TYPE的取值有限且固定，直接用VALUES生成所有需要的分组组合：

WITH required_combinations AS (
    SELECT 'Corporates' AS CUST_TYPE, 'SMALL' AS FINAL_SME_CATEGORY UNION ALL
    SELECT 'Corporates' AS CUST_TYPE, 'NATURAL PERSON' AS FINAL_SME_CATEGORY UNION ALL
    -- 按需添加其他CUST_TYPE与分类的组合
    SELECT 'Individuals' AS CUST_TYPE, 'SMALL' AS FINAL_SME_CATEGORY UNION ALL
    SELECT 'Individuals' AS CUST_TYPE, 'NATURAL PERSON' AS FINAL_SME_CATEGORY
)
SELECT 
    rc.CUST_TYPE,
    rc.FINAL_SME_CATEGORY,
    COALESCE(SUM(fsc.CUST_COMPENSATABLE_AMT), 0) AS TOTAL_SUM
FROM required_combinations rc
LEFT JOIN ddewd10s.FSCS_LIMIT_UTIL_SCV fsc 
    ON rc.CUST_TYPE = fsc.CUST_TYPE 
    AND rc.FINAL_SME_CATEGORY = fsc.FINAL_SME_CATEGORY
GROUP BY rc.CUST_TYPE, rc.FINAL_SME_CATEGORY
ORDER BY rc.CUST_TYPE, rc.FINAL_SME_CATEGORY;

方法二：动态生成组合（适合CUST_TYPE取值不固定的场景） #

如果CUST_TYPE的取值不确定，先从原表提取所有唯一的CUST_TYPE，再与目标FINAL_SME_CATEGORY做笛卡尔积生成全量组合：

WITH unique_cust_types AS (
    SELECT DISTINCT CUST_TYPE 
    FROM ddewd10s.FSCS_LIMIT_UTIL_SCV
),
required_combinations AS (
    SELECT 
        uct.CUST_TYPE,
        sc.FINAL_SME_CATEGORY
    FROM unique_cust_types uct
    CROSS JOIN (
        SELECT 'SMALL' AS FINAL_SME_CATEGORY UNION ALL
        SELECT 'NATURAL PERSON' AS FINAL_SME_CATEGORY
    ) sc
)
SELECT 
    rc.CUST_TYPE,
    rc.FINAL_SME_CATEGORY,
    COALESCE(SUM(fsc.CUST_COMPENSATABLE_AMT), 0) AS TOTAL_SUM
FROM required_combinations rc
LEFT JOIN ddewd10s.FSCS_LIMIT_UTIL_SCV fsc 
    ON rc.CUST_TYPE = fsc.CUST_TYPE 
    AND rc.FINAL_SME_CATEGORY = fsc.FINAL_SME_CATEGORY
GROUP BY rc.CUST_TYPE, rc.FINAL_SME_CATEGORY
ORDER BY rc.CUST_TYPE, rc.FINAL_SME_CATEGORY;

原理说明 #

1. 先用CTE生成所有必须显示的分组组合，确保每个需要的CUST_TYPE+FINAL_SME_CATEGORY都有一行。
2. 通过左连接（LEFT JOIN）关联组合表与原表，即使原表无对应记录，组合表的行仍会保留，原表字段值为NULL。
3. 最后用COALESCE将SUM返回的NULL转换为0，得到完整的统计结果。

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