如何在Postgres 13中用WITH RECURSIVE实现双向层级递归查询

Postgres 13 完全支持通过 WITH RECURSIVE 实现同时向上、向下的层级递归查询，你只需要在原有向下递归的逻辑外，新增向上递归的分支即可，完整实现如下：

最终查询语句 #

WITH RECURSIVE study_affiliation AS (
    -- 锚点：获取初始查询节点
    SELECT S.id, A.affiliated_id, S.title 
    FROM study S 
    LEFT JOIN affiliation A ON A.study_id = S.id 
    WHERE S.id = 2

    UNION

    -- 分支1：向下递归查询子节点
    SELECT S.id, A.affiliated_id, S.title 
    FROM study S 
    INNER JOIN affiliation A ON A.study_id = S.id 
    INNER JOIN study_affiliation SA ON A.affiliated_id = SA.id

    UNION

    -- 分支2：向上递归查询父节点
    SELECT S.id, A.affiliated_id, S.title 
    FROM study S 
    LEFT JOIN affiliation A ON A.study_id = S.id 
    INNER JOIN study_affiliation SA ON S.id = SA.affiliated_id
)
SELECT id, affiliated_id, title 
FROM study_affiliation 
ORDER BY id;

执行结果 #

执行上述语句后将返回你需要的全量层级数据：

id | affiliated_id |  title
----+---------------+---------
  1 |          null | study 1
  2 |             1 | study 2
  3 |             2 | study 3

逻辑说明 #

• 锚点部分固定查询你指定的初始节点（示例中为id=2的study记录）
• 第一个递归分支保留你原有的向下查询逻辑：匹配所有父级ID等于当前递归集合节点ID的子节点
• 第二个递归分支实现向上查询逻辑：匹配所有ID等于当前递归集合节点父级ID的父节点，持续向上追溯直到无父级为止
• 用UNION替代UNION ALL可以自动去重，同时避免数据存在环时出现死循环问题

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