如何基于父子关系对同表数据求和并更新TOTAL_AMOUNT列

我有一张存储家庭月度支出的表，表中存在父子关系。需要计算各节点的总支出，更新表中的TOTAL_AMOUNT列。

表结构与测试数据 #

CREATE TABLE PARENT_CHILD
(
   ID             NUMBER (10),
   PARENT_ID      NUMBER (10),
   OWN_AMOUNT     NUMBER (20),
   TOTAL_AMOUNT   NUMBER (20)
);

INSERT INTO PARENT_CHILD VALUES (1, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (2, NULL, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (3, NULL, 10000, NULL);
INSERT INTO PARENT_CHILD VALUES (4, 3, 2000, NULL);
INSERT INTO PARENT_CHILD VALUES (5, NULL, 100000, NULL);
INSERT INTO PARENT_CHILD VALUES (6, 5, 25000, NULL);
INSERT INTO PARENT_CHILD VALUES (7, 6, 15000, NULL);
INSERT INTO PARENT_CHILD VALUES (8, 5, 30000, NULL);
INSERT INTO PARENT_CHILD VALUES (9, 5, 20000, NULL);
INSERT INTO PARENT_CHILD VALUES (10, 5, 8000, NULL);
INSERT INTO PARENT_CHILD VALUES (11, 10, 2000, NULL);

期望更新结果 #

可以使用**递归CTE（公共表表达式）**计算各节点总支出，再通过MERGE语句更新原表的TOTAL_AMOUNT列。以下是适用于Oracle数据库的脚本：

WITH RECURSIVE EXPENSE_HIERARCHY AS (
    -- 锚点成员：筛选所有叶子节点（无下级节点），总支出等于自身金额
    SELECT 
        ID,
        PARENT_ID,
        OWN_AMOUNT,
        OWN_AMOUNT AS TOTAL_AMOUNT,
        1 AS LEVEL_DEPTH
    FROM PARENT_CHILD
    WHERE ID NOT IN (SELECT PARENT_ID FROM PARENT_CHILD WHERE PARENT_ID IS NOT NULL)
    
    UNION ALL
    
    -- 递归成员：向上遍历父节点，累加自身金额与所有子节点的总支出
    SELECT 
        p.ID,
        p.PARENT_ID,
        p.OWN_AMOUNT,
        p.OWN_AMOUNT + SUM(c.TOTAL_AMOUNT) AS TOTAL_AMOUNT,
        c.LEVEL_DEPTH + 1 AS LEVEL_DEPTH
    FROM PARENT_CHILD p
    JOIN EXPENSE_HIERARCHY c ON p.ID = c.PARENT_ID
    GROUP BY p.ID, p.PARENT_ID, p.OWN_AMOUNT, c.LEVEL_DEPTH
)
-- MERGE语句将计算结果更新到原表
MERGE INTO PARENT_CHILD t
USING (
    SELECT ID, TOTAL_AMOUNT 
    FROM EXPENSE_HIERARCHY
    UNION ALL
    -- 补充处理无下级节点的根节点，避免锚点成员遗漏
    SELECT ID, OWN_AMOUNT AS TOTAL_AMOUNT
    FROM PARENT_CHILD
    WHERE PARENT_ID IS NULL 
      AND ID NOT IN (SELECT PARENT_ID FROM PARENT_CHILD WHERE PARENT_ID IS NOT NULL)
) s
ON (t.ID = s.ID)
WHEN MATCHED THEN UPDATE SET t.TOTAL_AMOUNT = s.TOTAL_AMOUNT;

-- 提交更新
COMMIT;

脚本说明 #

1. 锚点成员：先定位所有叶子节点，这类节点没有子节点，总支出直接等于自身的OWN_AMOUNT。
2. 递归成员：从叶子节点向上遍历父节点，将父节点自身金额与所有子节点的总支出求和，得到父节点的总支出。
3. MERGE更新：把递归计算出的总支出同步到原表，同时补充处理没有子节点的根节点，确保所有节点都被覆盖。

执行完脚本后，表中TOTAL_AMOUNT列会更新为期望结果。

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