如何提取父子列对的层级关联并实现ID唯一单行展示？

原始数据表 #

需求说明 #

提取aid与bid的层级关联，仅保留完整连通路径，每个ID只出现在一行中，预期输出：

原代码问题 #

你写的递归CTE会生成所有中间子路径（如1,2、1,2,3、7,8），因为递归过程中每一步都会输出当前路径，且没有处理分支节点的合并逻辑。

解决方案 #

通过收集每个连通分量的所有节点，去重后按层级排序拼接，可以得到符合要求的完整路径：

WITH RECURSIVE node_tree AS (
    -- 初始化：找到所有无父节点的根节点，记录初始节点和层级
    SELECT 
        aid AS root,
        aid AS node,
        CAST(aid AS TEXT) AS node_list,
        1 AS level
    FROM tmp 
    WHERE aid NOT IN (SELECT bid FROM tmp)
    UNION ALL
    -- 递归遍历：收集所有可达子节点，避免循环
    SELECT 
        nt.root,
        h.bid,
        nt.node_list || ',' || CAST(h.bid AS TEXT),
        nt.level + 1
    FROM node_tree nt
    JOIN tmp h ON nt.node = h.aid
    WHERE NOT nt.node_list LIKE '%,' || CAST(h.bid AS TEXT) || '%'
),
-- 对每个根节点的所有节点去重，按层级+节点值排序后拼接
unique_paths AS (
    SELECT 
        root,
        string_agg(DISTINCT node, ',' ORDER BY level, node) AS path
    FROM node_tree
    GROUP BY root
)
SELECT path FROM unique_paths ORDER BY path;

逻辑说明 #

1. 根节点识别：先筛选出所有没有父节点（即不在bid列中）的aid作为连通分量的起点。
2. 递归遍历：从根节点出发，遍历所有可达的子节点，记录每个节点的层级和已访问节点列表（避免循环）。
3. 合并去重：对每个根节点的所有节点去重，按层级顺序排序后拼接成完整路径，确保每个ID只出现一次，且路径包含整个连通分量。

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