含NULL值的双列关联及GROUPING SETS结果匹配问题求解

核心问题 #

1. 原查询子查询中WHERE LINE_OF_BUSINESS_ENG is not null过滤掉了全量汇总的NULL行，导致主查询无法关联到全局汇总的节省金额。
2. 主查询的WHERE CLAIM_TYPE_GROUP is not NULL直接排除了CLAIM_TYPE为NULL的分组小计行，破坏了分组集的完整结构。
3. 关联条件的NULL匹配逻辑冗余，且主查询分组时包含聚合结果字段SAVINGS，引发分组错误。

修正后的SQL #

SELECT 
    COALESCE(c.LOB, rr.LOB) AS LOB,
    COALESCE(c.CLAIM_TYPE, rr.CLAIM_TYPE) AS CLAIM_TYPE,
    COUNT(c.CLAIM_NUMBER) AS COUNT_CLAIM_NUMBER,
    SUM(rr.SUM_RR_SAVINGS) AS SUM_RR_SAVINGS_AMOUNT
FROM (
    -- 对索赔表生成完整分组集
    SELECT 
        LOB,
        CLAIM_TYPE,
        CLAIM_NUMBER
    FROM 新索赔表
    GROUP BY
        GROUPING SETS(
            (LOB, CLAIM_TYPE, CLAIM_NUMBER),
            (LOB),
            ()
        )
) c
FULL JOIN (
    -- 对节省金额表生成匹配分组集
    SELECT
        LOB,
        CLAIM_TYPE,
        SUM(RR_SAVING_AMOUNT) AS SUM_RR_SAVINGS
    FROM 索赔节省金额表
    GROUP BY
        GROUPING SETS(
            (LOB, CLAIM_TYPE),
            (LOB),
            ()
        )
) rr 
    -- 处理NULL值的匹配逻辑
    ON (c.LOB = rr.LOB OR (c.LOB IS NULL AND rr.LOB IS NULL))
    AND (c.CLAIM_TYPE = rr.CLAIM_TYPE OR (c.CLAIM_TYPE IS NULL AND rr.CLAIM_TYPE IS NULL))
-- 按统一分组集聚合
GROUP BY
    GROUPING SETS(
        (COALESCE(c.LOB, rr.LOB), COALESCE(c.CLAIM_TYPE, rr.CLAIM_TYPE)),
        (COALESCE(c.LOB, rr.LOB)),
        ()
    )
-- 对齐期望结果的排序顺序
ORDER BY 
    CASE WHEN COALESCE(c.LOB, rr.LOB) IS NULL THEN 0 ELSE 1 END,
    COALESCE(c.LOB, rr.LOB),
    CASE WHEN COALESCE(c.CLAIM_TYPE, rr.CLAIM_TYPE) IS NULL THEN 0 ELSE 1 END,
    COALESCE(c.CLAIM_TYPE, rr.CLAIM_TYPE)

关键修正说明 #

• 分别对两张表独立生成完整分组集，再用FULL JOIN关联，确保所有汇总行都能匹配。
• 移除所有过滤NULL的WHERE条件，保留分组集生成的汇总行。
• 用COALESCE统一两侧字段值，避免单侧NULL导致输出字段为空。
• 主查询分组仅使用业务线和索赔类型，不再包含聚合结果字段，避免分组错误。
• 添加ORDER BY语句，让输出顺序与期望结果完全一致。

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