PostgreSQL中使用多递归CTE双向遍历关联数据

表结构与测试数据 #

首先是links表的定义和测试数据：

CREATE TABLE links
(
    linkid serial primary key,
    patientid integer NOT NULL,
    linkto integer NOT NULL
);

INSERT INTO links (patientid, linkto)
VALUES  (1,2), (1,3), (1,4), (1,5), (1,6);

INSERT INTO links (patientid, linkto)
VALUES  (2,7), (2,8), (2,9), (2,10), (2,11);
 
INSERT INTO links (patientid, linkto)
VALUES  (3,12), (3,13), (3,14), (3,15), (3,16);
  
INSERT INTO links (patientid, linkto)
VALUES  (4,17), (4,18), (4,19), (4,20), (4,21);

原有正向递归逻辑 #

当起始patientid为1时，以下递归CTE可以正确枚举其直接关联及所有下游ID：

WITH RECURSIVE _IN (patientid) AS (
        VALUES (1)
), 
linked (linkid, patientid, linkto) AS (
    SELECT k.linkid, k.patientid, k.linkto 
       FROM _IN n 
       JOIN links k ON k.patientid = n.patientid
    UNION ALL
    SELECT LL.linkid, LL.patientid, LL.linkto  FROM linked L
    INNER JOIN links LL ON L.linkto = LL.patientid  
) SELECT * FROM linked;

需求与问题 #

当起始ID为2时，需要同时枚举：

• 2的正向关联ID（即2的下游：7、8、9、10、11及它们的关联）
• 2的反向关联ID（即关联到2的1，以及1的所有关联：3、4、5、6及它们的关联）

原有尝试的多递归CTE写法存在两个核心问题：

1. 连接条件错误：backlink部分的L.linkto = L.linkto是恒成立条件，会导致无限循环
2. 分开处理正向和反向逻辑，无法有效避免重复遍历，进而引发递归无法终止

解决方案：单递归CTE处理双向关联 #

通过在递归过程中跟踪已访问的patientid集合，同时处理正向和反向关联，避免循环和重复：

WITH RECURSIVE linked_nodes AS (
    -- 初始节点：起始patientid=2，同时记录已访问集合
    SELECT 
        k.linkid, 
        k.patientid, 
        k.linkto,
        -- 用数组记录已访问的patientid，避免重复遍历
        ARRAY[k.patientid, k.linkto]::integer[] AS visited
    FROM links k
    WHERE k.patientid = 2

    UNION ALL

    SELECT
        ll.linkid,
        ll.patientid,
        ll.linkto,
        -- 将新访问的patientid加入已访问数组
        ln.visited || ll.linkto
    FROM linked_nodes ln
    -- 正向关联：当前节点的linkto作为新的patientid，且未被访问过
    JOIN links ll ON ln.linkto = ll.patientid
        AND NOT ll.patientid = ANY(ln.visited)

    UNION ALL

    SELECT
        ll.linkid,
        ll.patientid,
        ll.linkto,
        ln.visited || ll.patientid
    FROM linked_nodes ln
    -- 反向关联：找所有linkto等于当前patientid的记录，且未被访问过
    JOIN links ll ON ln.patientid = ll.linkto
        AND NOT ll.patientid = ANY(ln.visited)
)
-- 最终查询去重（因为可能存在双向关联导致的重复记录）
SELECT DISTINCT linkid, patientid, linkto FROM linked_nodes;

逻辑说明 #

1. 初始节点：从起始ID=2的直接关联记录开始，同时初始化已访问数组，包含当前的patientid和linkto
2. 正向递归：遍历当前节点linkto对应的所有下游关联，确保新的patientid未被访问过
3. 反向递归：遍历所有指向当前节点patientid的上游关联，同样确保新的patientid未被访问过
4. 去重处理：使用DISTINCT避免因双向遍历产生的重复记录

这个方案可以一次性覆盖所有正向和反向的关联节点，同时通过已访问集合彻底避免递归循环。

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