PostgreSQL使用单个递归CTE计算树形结构各节点子树值总和

该需求完全可以通过单个递归CTE实现，你最初的自底向上汇总思路方向正确，但写法不符合递归CTE的语法限制，调整遍历逻辑即可。

• 未明确定义锚点查询的输出字段，递归关联的父子关系写反
• 递归CTE的递归成员中，不支持直接对递归自引用的结果做聚合运算，会触发SQL语法错误
• 未正确处理递归终止条件，容易出现死循环或者漏算节点值

以下写法适配你给出的测试数据集（注：你提到的id<0识别叶子节点规则和测试数据不匹配，测试数据中叶子id均为正整数，后续会说明规则适配方法）：

WITH RECURSIVE node_hierarchy AS (
    -- 锚点：加载所有节点自身作为子树统计的起点
    SELECT
        id AS root_id,
        value
    FROM public.data

    UNION ALL

    -- 递归：向上遍历所有父节点，将当前节点值透传到每一级上级
    SELECT
        d.parent_id AS root_id,
        nh.value
    FROM node_hierarchy nh
    JOIN public.data d ON nh.root_id = d.id
    WHERE d.parent_id IS NOT NULL -- 遍历到根节点（无父节点）时终止
)
-- 按子树根节点分组，累加自身及所有下属节点的值
SELECT
    root_id AS id,
    SUM(value) AS "sum(value)"
FROM node_hierarchy
GROUP BY root_id
ORDER BY id;

如果你的实际业务场景确实使用id < 0作为叶子节点的判断条件，只需要把锚点部分的查询替换为如下语句即可，递归逻辑和最终聚合不需要改动：

-- 适配id<0为叶子节点的锚点写法
SELECT
    id AS root_id,
    value
FROM public.data
WHERE id < 0

用你提供的测试数据执行上述SQL，输出结果完全匹配预期：

• id=5：sum(value)=1
• id=4：sum(value)=3（自身值2 + 子节点5的值1）
• id=3：sum(value)=4
• id=2：sum(value)=0
• id=1：sum(value)=15（自身值8 + 子节点3的值4 + 子节点4的汇总值3）

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