基于父子表创建PostgreSQL层级表的实现方案咨询

需求说明 #

需要将给定的父子（F/S）表转换为指定结构的层级表，示例父子表数据如下：

预期生成的层级表结果：

实现方案 #

利用PostgreSQL的**递归CTE（WITH RECURSIVE）**特性处理层级关系，结合叶子节点标记实现预期结构：

步骤1：创建示例表并插入数据 #

-- 创建父子表
CREATE TABLE fs_table (
    F VARCHAR(1),
    S VARCHAR(1)
);

-- 插入示例数据
INSERT INTO fs_table (F, S) VALUES
('a', 'b'),
('b', 'c'),
('b', 'd'),
('b', 'e'),
('e', 'f'),
('e', 'g'),
('b', 'm'),
('z', 'n'),
('m', 'k');

步骤2：递归生成层级表 #

WITH leaf_nodes AS (
    -- 标记所有叶子节点（无后续子节点的节点）
    SELECT S AS node
    FROM fs_table
    WHERE S NOT IN (SELECT F FROM fs_table)
),
recursive_paths AS (
    -- 基础查询：从根节点（无父节点的节点）出发，构建初始路径
    SELECT
        F AS start_node,
        ARRAY[F, S] AS path,
        CASE WHEN S IN (SELECT node FROM leaf_nodes) THEN true ELSE false END AS is_leaf_path
    FROM fs_table
    WHERE F NOT IN (SELECT S FROM fs_table)
    UNION ALL
    -- 递归查询：扩展路径，处理非叶子节点的子节点
    SELECT
        -- 如果当前路径终点是叶子节点，新路径起始节点改为当前子节点；否则保留原起始节点
        CASE WHEN rp.is_leaf_path THEN fs.F ELSE rp.start_node END,
        -- 构建新路径：叶子节点路径直接取当前父子对，非叶子节点路径继续扩展
        CASE WHEN rp.is_leaf_path THEN ARRAY[fs.F, fs.S] ELSE rp.path || fs.S END,
        -- 标记新路径终点是否为叶子节点
        CASE WHEN fs.S IN (SELECT node FROM leaf_nodes) THEN true ELSE false END
    FROM recursive_paths rp
    JOIN fs_table fs ON rp.path[array_length(rp.path, 1)] = fs.F
    WHERE NOT rp.is_leaf_path
)
-- 将路径数组拆分为层级列，NULL自动显示为空
SELECT
    path[1] AS L,
    path[2] AS L1,
    path[3] AS L2,
    path[4] AS L3
FROM recursive_paths
ORDER BY path;

思路解释 #

1. 叶子节点标记：通过CTE筛选出所有没有子节点的节点（仅出现在S列，未出现在F列），用于判断路径是否需要终止。
2. 递归路径构建：
   • 从根节点（仅出现在F列，未出现在S列）开始，生成根节点到直接子节点的初始路径。
   • 对于非叶子节点的路径，继续扩展到其子节点；如果扩展后的子节点是叶子节点，则单独生成以该非叶子节点为起点的路径（如e→f），否则继续延长原根节点路径（如a→b→m→k）。
3. 路径拆分：将递归生成的路径数组按位置拆分为对应的层级列，不足4层的位置自动填充NULL，最终匹配预期的层级表结构。

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