Postgres树形表双向递归查询异常：升序降序结果不符预期

表结构与测试数据 #

create table graph_example (input int null, output int );
insert into graph_example (input, output) values
(null, 1),
(1, 2),
(2, 3 ),
(3, 4 ),
(null, 7 ),
(7,8),
(8, 4 ),
(null, 10 ),
(10, 11 ),
(11, 4),
(3, 15),
(25, 15),
(26, 15),
(15, 4 );

升序（向下遍历）查询修正 #

原问题分析 #

原查询初始仅选取input=1的边（即1→2），遗漏了起点1本身；路径拼接逻辑错误，循环检测的节点判断不准确，无法生成预期的完整路径序列。

修正后查询 #

WITH RECURSIVE cte (initial_id, current_id, level, path, visited) AS (
    -- 初始节点：起点1本身
    SELECT 
        1 AS initial_id,
        1 AS current_id,
        1 AS level,
        '1' AS path,
        ARRAY[1] AS visited
    UNION ALL
    -- 递归遍历下游节点
    SELECT 
        c.initial_id,
        g.output AS current_id,
        c.level + 1,
        c.path || '->' || g.output,
        c.visited || g.output
    FROM cte c
    JOIN graph_example g ON c.current_id = g.input
    -- 避免循环：当前节点未在已访问列表中
    WHERE NOT (g.output = ANY(c.visited))
)
SELECT initial_id, level, path
FROM cte
ORDER BY level;

结果说明 #

执行后将得到预期路径及扩展分支：

• 1
• 1->2
• 1->2->3
• 1->2->3->4
• 1->2->3->15
• 1->2->3->15->4

降序（向上回溯）查询修正 #

原问题分析 #

原查询初始逻辑错误，未将4作为起点，直接选取指向4的边导致路径缺少4本身；列定义混乱，递归关联条件不正确，无法完整回溯所有上游路径。

修正后查询 #

WITH RECURSIVE cte (target_id, current_id, level, path, visited) AS (
    -- 初始节点：目标节点4本身
    SELECT 
        4 AS target_id,
        4 AS current_id,
        1 AS level,
        '4' AS path,
        ARRAY[4] AS visited
    UNION ALL
    -- 递归回溯上游节点
    SELECT 
        c.target_id,
        g.input AS current_id,
        c.level + 1,
        c.path || '->' || g.input,
        c.visited || g.input
    FROM cte c
    JOIN graph_example g ON c.current_id = g.output
    -- 避免循环：上游节点未在已访问列表中，排除null节点
    WHERE g.input IS NOT NULL AND NOT (g.input = ANY(c.visited))
)
SELECT path
FROM cte
ORDER BY path;

结果说明 #

执行后将得到所有回溯路径：

• 4
• 4->3
• 4->3->2
• 4->3->2->1
• 4->8
• 4->8->7
• 4->11
• 4->11->10
• 4->15
• 4->15->3
• 4->15->3->2
• 4->15->3->2->1
• 4->15->25
• 4->15->26

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