根据两字段关联累加value的递归SQL查询结果错误如何修复

你写的递归查询存在两处核心错误：

• 递归CTE没有传递前序节点的累加值，仅单独取出每一行的原始value，完全没有实现累加逻辑
• 最终聚合时错误将value字段加入GROUP BY条件，每个分组仅包含一行数据，SUM计算结果自然和原始value一致

修复后的完整代码 #

DROP TABLE IF EXISTS turbi.flowAdd_v2;
CREATE TABLE turbi.flowAdd_v2 AS
WITH RECURSIVE additionflow (id, start_pt, end_pt, value, addition)
AS(
    -- 递归初始节点：无匹配前驱节点的行，初始累加值为自身value
    SELECT 
        tu.id,
        tu.start_pt,
        tu.end_pt,
        tu.value,
        tu.value AS addition
    FROM turbi."input" tu
    LEFT JOIN turbi."input" t2 
    ON tu.start_pt = t2.end_pt
    WHERE t2.end_pt IS NULL
    UNION ALL
    -- 递归分支：匹配到连续节点时，累加当前value到前序结果
    SELECT
        tu.id,
        tu.start_pt,
        tu.end_pt,
        tu.value,
        af.addition + tu.value AS addition
    FROM additionflow af
    JOIN turbi."input" tu
    ON af.end_pt = tu.start_pt
)
-- 直接读取递归结果即可，无需额外聚合
SELECT id, value, addition FROM additionflow ORDER BY id;

SELECT * FROM turbi.flowAdd_v2;

关键修改说明 #

1. 递归CTE新增addition字段专门存储累计值，初始节点的累计值等于自身value
2. 递归分支不再直接取当前行的value，而是将上一层的累计值与当前行value相加，实现连续累加
3. 移除错误的GROUP BY逻辑，递归执行完成后直接返回的addition字段就是预期结果

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