能否使用递归CTE生成锯齿状关联数据的链路路径输出？

原始数据 #

假设表link_table的结构与数据如下：

期望输出 #

需要生成所有连通节点，每行一个：

当前代码问题 #

你当前的递归CTE逻辑有误，仅返回部分节点：

with recursive links (linkval) as (
  select c1 from link_table where c1 = 'A'
  union 
  select t.c2 
  from links l
  join link_table t 
    on (t.c1 <> l.linkval or t.c2 <> l.linkval) 
) select linkval as L from links;

输出仅包含A、B、D、F，漏掉了C和E，原因是递归条件未正确遍历所有关联节点，也没有循环防护逻辑。

正确的递归CTE实现 #

要遍历整个连通节点集合，需将数据当作无向图处理，同时追踪已访问节点避免循环：

WITH RECURSIVE links(linkval, visited) AS (
  -- 锚点：从节点A出发，记录已访问节点集合
  SELECT c1, ARRAY[c1]
  FROM link_table
  WHERE c1 = 'A'
  UNION ALL
  -- 递归：找到所有与当前节点关联的未访问节点
  SELECT 
    CASE WHEN t.c1 = l.linkval THEN t.c2 ELSE t.c1 END AS linkval,
    l.visited || CASE WHEN t.c1 = l.linkval THEN t.c2 ELSE t.c1 END
  FROM links l
  JOIN link_table t 
    ON t.c1 = l.linkval OR t.c2 = l.linkval
  -- 排除已访问节点，避免循环遍历
  WHERE CASE WHEN t.c1 = l.linkval THEN t.c2 ELSE t.c1 END <> ALL(l.visited)
)
-- 去重后得到所有连通节点
SELECT DISTINCT linkval AS L FROM links;

逻辑说明 #

1. 锚点成员：从起始节点A出发，用数组visited记录已访问节点。
2. 递归成员：通过JOIN匹配所有与当前节点关联的节点（含当前节点作为C1对应的C2，以及作为C2对应的C1），仅加入未访问过的节点。
3. 去重处理：用DISTINCT消除不同路径下重复出现的节点，最终得到完整的连通节点列表。

执行该代码后，即可得到期望的所有节点输出。

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