PostgreSQL递归CTE中如何用UNION ALL实现UNION去重并保留层级路径

出现重复数据的核心原因是你的表中存在多条指向同一子节点的路径（比如节点7既可以通过1→2→3→4→5→6→7的长路径到达，也可以通过1→7的短路径到达），UNION ALL不会自动去重，所以会生成多条对应同一child的记录。另外递归CTE的递归部分不允许子查询引用CTE自身，所以你之前写的EXISTS条件会触发语法错误。

你可以先通过递归CTE生成所有可能的遍历路径，再在外层对child字段去重即可，这里提供两种通用实现：

方案1：PostgreSQL专用（DISTINCT ON语法更简洁） #

with recursive cte as (
    select
        parent,
        child,
        0 as level,
        array[parent, child] as path
    from stack
    where parent = 1
    union all
    select
        s.parent,
        s.child,
        c.level + 1,
        c.path || s.child
    from cte c
    inner join stack s on c.child = s.parent
    where c.child is not null
        -- 可选加该条件避免数据存在循环时死循环
        and s.child != all(c.path)
)
-- DISTINCT ON 按child分组，每组取排序后的第一条
select distinct on (child) parent, child, level, path
from cte
-- 按level升序取每个节点的最短路径，如需最长路径改为降序即可
order by child, level asc;

方案2：全数据库通用（窗口函数实现） #

with recursive cte as (
    select
        parent,
        child,
        0 as level,
        array[parent, child] as path
    from stack
    where parent = 1
    union all
    select
        s.parent,
        s.child,
        c.level + 1,
        c.path || s.child
    from cte c
    inner join stack s on c.child = s.parent
    where c.child is not null
        and s.child != all(c.path)
),
ranked_cte as (
    select 
        *,
        row_number() over(partition by child order by level asc) as rn
    from cte
)
select parent, child, level, path
from ranked_cte
where rn = 1;

执行上述语句后，返回的child列表和你之前用UNION得到的去重结果完全一致，同时会携带对应节点的深度和遍历路径。

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