如何编写SQL根据列关联关系按客户分组计算REFINED与UNREFINED金额

实现方案 #

我们可以通过拆分计算逻辑的方式实现需求，以下是兼容绝大多数关系型数据库（MySQL、PostgreSQL、Oracle等）的写法：

写法1：CTE 实现（可读性更高）

WITH refined_total AS (
    -- 计算每个客户的REFINED总金额
    SELECT 
        CUST_ID,
        SUM(AMOUNT) AS REFINED_AMOUNT
    FROM 替换为你的实际表名
    WHERE TYPE = 'REFINED'
    GROUP BY CUST_ID
),
raw_refined_mapping AS (
    -- 统计每个无父节点的RAW记录，关联的REFINED总金额
    SELECT 
        r.CUST_ID,
        r.ITEM_ID,
        r.AMOUNT AS raw_amount,
        COALESCE(SUM(rf.AMOUNT), 0) AS linked_refined_amount
    FROM 替换为你的实际表名 r
    LEFT JOIN 替换为你的实际表名 rf 
        ON r.ITEM_ID = rf.PARENT_ID 
        AND rf.TYPE = 'REFINED'
    WHERE r.TYPE = 'RAW' AND r.PARENT_ID IS NULL
    GROUP BY r.CUST_ID, r.ITEM_ID, r.AMOUNT
),
unrefined_total AS (
    -- 计算每个客户的UNREFINED总金额
    SELECT 
        CUST_ID,
        SUM(raw_amount - linked_refined_amount) AS UNREFINED_AMOUNT
    FROM raw_refined_mapping
    GROUP BY CUST_ID
)
-- 合并结果输出
SELECT 
    rt.CUST_ID,
    rt.REFINED_AMOUNT,
    ut.UNREFINED_AMOUNT
FROM refined_total rt
JOIN unrefined_total ut ON rt.CUST_ID = ut.CUST_ID
ORDER BY rt.CUST_ID;

写法2：子查询实现（兼容不支持CTE的低版本数据库）

SELECT 
    rt.CUST_ID,
    rt.REFINED_AMOUNT,
    ut.UNREFINED_AMOUNT
FROM (
    SELECT 
        CUST_ID,
        SUM(AMOUNT) AS REFINED_AMOUNT
    FROM 替换为你的实际表名
    WHERE TYPE = 'REFINED'
    GROUP BY CUST_ID
) rt
JOIN (
    SELECT 
        CUST_ID,
        SUM(raw_amount - linked_refined_amount) AS UNREFINED_AMOUNT
    FROM (
        SELECT 
            r.CUST_ID,
            r.AMOUNT AS raw_amount,
            COALESCE(SUM(rf.AMOUNT), 0) AS linked_refined_amount
        FROM 替换为你的实际表名 r
        LEFT JOIN 替换为你的实际表名 rf 
            ON r.ITEM_ID = rf.PARENT_ID 
            AND rf.TYPE = 'REFINED'
        WHERE r.TYPE = 'RAW' AND r.PARENT_ID IS NULL
        GROUP BY r.CUST_ID, r.ITEM_ID, r.AMOUNT
    ) t
    GROUP BY CUST_ID
) ut ON rt.CUST_ID = ut.CUST_ID
ORDER BY rt.CUST_ID;

逻辑说明 #

1. 计算REFINED_AMOUNT时直接筛选所有REFINED类型的记录按客户求和，包含无PARENT_ID的独立REFINED记录，符合规则要求。
2. 计算UNREFINED_AMOUNT时先关联每个RAW节点和对应的REFINED子节点，用每个RAW的金额减去对应已加工的总金额，再按客户汇总，自动处理RAW无关联REFINED的场景。
3. 最终输出结果和你给出的预期值完全一致。

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