如何在PostgreSQL邻接表中查询指定节点的所有关联节点？

表结构与初始数据 #

创建存储树形节点的表：

CREATE TABLE example (
    example_name varchar(50),
    example_parent varchar(50)
);

INSERT INTO example (example_name, example_parent)
VALUES 
('e1', NULL), 
('e2', 'e1'),
('e3', NULL), 
('e4', NULL),
('e5', NULL),
('e6', 'e5'),
('e7', 'e6'),
('e8', 'e7'),
('e9', NULL);

表中现有数据：

查询需求 #

根据指定的example_name，查询该节点的所有关联节点（包括所有祖先节点和后代节点），返回格式如下：

• 指定e1时，返回其后代节点：

• 指定e8时，返回其祖先节点：

• 指定e9时，无关联节点：

解决方案：递归CTE查询 #

使用递归公共表达式（CTE）分别遍历目标节点的祖先链和后代链，合并结果后用字符串拼接输出：

-- 替换下方的'指定节点'为实际要查询的example_name值
WITH recursive_descendants AS (
    -- 初始节点
    SELECT example_name, example_parent
    FROM example
    WHERE example_name = '指定节点'
    UNION ALL
    -- 递归获取所有后代节点
    SELECT e.example_name, e.example_parent
    FROM example e
    JOIN recursive_descendants rd ON e.example_parent = rd.example_name
),
recursive_ancestors AS (
    -- 初始节点
    SELECT example_name, example_parent
    FROM example
    WHERE example_name = '指定节点'
    UNION ALL
    -- 递归获取所有祖先节点
    SELECT e.example_name, e.example_parent
    FROM example e
    JOIN recursive_ancestors ra ON e.example_name = ra.example_parent
),
all_associations AS (
    -- 合并后代与祖先节点，排除自身
    SELECT example_name FROM recursive_descendants WHERE example_name != '指定节点'
    UNION
    SELECT example_name FROM recursive_ancestors WHERE example_name != '指定节点'
)
-- 拼接结果为逗号分隔的字符串
SELECT 
    '指定节点' AS example_name,
    STRING_AGG(example_name, ', ') AS associations
FROM all_associations;

结果验证 #

• 替换'指定节点'为'e1'，会返回后代节点e2，符合预期。
• 替换为'e8'，会返回祖先节点e7、e6、e5，符合预期。
• 替换为'e9'，无关联节点，associations字段为空，符合预期。

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