Oracle层级查询：如何计算各节点的子节点成本总和？

方法1：递归CTE（最直观简便） #

Oracle 11g及以上支持递归CTE，逻辑完全贴合需求：先计算叶子节点的CALCULATED_COST，再向上递归计算父节点的总和（直接子节点的CALCULATED_COST之和）。

WITH recursive_calc AS (
    -- 锚点：叶子节点（没有子节点的节点）
    SELECT 
        id, parent_id, label, cost,
        cost AS calculated_cost
    FROM objective
    WHERE id NOT IN (SELECT DISTINCT parent_id FROM objective WHERE parent_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归：非叶子节点，累加直接子节点的calculated_cost
    SELECT 
        p.id, p.parent_id, p.label, p.cost,
        SUM(c.calculated_cost) AS calculated_cost
    FROM objective p
    JOIN recursive_calc c ON p.id = c.parent_id
    GROUP BY p.id, p.parent_id, p.label, p.cost
)
-- 按原层级顺序输出结果
SELECT 
    rc.id, rc.parent_id, rc.label, rc.cost, rc.calculated_cost
FROM recursive_calc rc
START WITH rc.parent_id IS NULL
CONNECT BY PRIOR rc.id = rc.parent_id
ORDER SIBLINGS BY rc.id;

方法2：原查询基础上结合子查询 #

如果不想用递归CTE，可以在原CONNECT BY查询中嵌套子查询，针对每个非叶子节点计算直接子节点的CALCULATED_COST总和：

SELECT 
    b.id, b.parent_id, b.label, b.cost,
    CASE
        -- 叶子节点直接取自身cost
        WHEN CONNECT_BY_ISLEAF = 1 THEN b.cost
        -- 非叶子节点计算直接子节点的calculated_cost之和
        ELSE (
            SELECT SUM(
                CASE WHEN CONNECT_BY_ISLEAF = 1 THEN c.cost ELSE 0 END
            )
            FROM objective c
            START WITH c.parent_id = b.id
            CONNECT BY PRIOR c.id = c.parent_id
        )
    END AS calculated_cost
FROM objective b
START WITH b.parent_id IS NULL
CONNECT BY PRIOR b.id = b.parent_id
ORDER SIBLINGS BY b.id;

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