PostgreSQL递归查询实现层级节点更新后向上重算父节点值

数据老陈

2026-10-5

解决方案

实现逻辑

第一步：递归向上查找更新节点的所有祖先节点，标记层级（离更新节点越近层级值越小）
第二步：从距离更新节点最近的父节点开始逐层向上计算新值，已更新过的子节点使用新值，未更新的子节点使用表中存储的原值
第三步：输出所有重算完成的父节点信息

正确SQL代码

WITH RECURSIVE ancestors AS (
    -- 定位更新节点的直接父节点作为递归起点，标记层级为1
    SELECT id, parent_id, name, 1 AS level
    FROM hierarchy
    WHERE id = (SELECT parent_id FROM hierarchy WHERE id = 5)
    UNION ALL
    -- 递归向上查找所有上层祖先节点，层级依次+1
    SELECT h.id, h.parent_id, h.name, a.level + 1 AS level
    FROM hierarchy h
    JOIN ancestors a ON h.id = a.parent_id
),
calculated_values AS (
    -- 先计算最底层父节点（离更新节点最近）的新值：直接汇总所有子节点的存储值
    SELECT a.id, a.parent_id, a.name, SUM(h.value) AS new_value, a.level
    FROM ancestors a
    LEFT JOIN hierarchy h ON h.parent_id = a.id
    WHERE a.level = 1
    GROUP BY a.id, a.parent_id, a.name, a.level
    UNION ALL
    -- 逐层向上计算上层父节点新值：已重算的子节点用新值，未重算的用存储原值求和
    SELECT a.id, a.parent_id, a.name, 
           SUM(COALESCE(cv.new_value, h.value)) AS new_value, a.level
    FROM ancestors a
    LEFT JOIN hierarchy h ON h.parent_id = a.id
    LEFT JOIN calculated_values cv ON h.id = cv.id
    WHERE a.level = (SELECT MAX(level) FROM calculated_values) + 1
    GROUP BY a.id, a.parent_id, a.name, a.level
)
SELECT id, name, new_value AS value
FROM calculated_values
ORDER BY id;