PostgreSQL递归查询获取指定ID对应所有子节点的实现方法

场景说明 #

• 业务需求：Oracle迁移PostgreSQL过程中，需要实现传入指定节点ID，返回该节点自身及所有层级子节点的树形查询
• 表结构：业务树形表共3个字段：id（节点唯一标识）、id_parent（父节点ID，根节点父ID为0）、name（节点名称）
• 示例数据：

• 预期效果：
  • 传入id=3时，返回ID集合：3、4、5
  • 传入id=1时，返回ID集合：1、3、4、5

原有SQL问题 #

原有递归SQL只能返回全量节点，核心问题有三个：

1. 递归锚点部分直接查询全表，没有指定起始节点，导致递归从所有节点开始遍历
2. 递归关联逻辑写反：cat.id_parent = table.id是从子节点向上查找父节点的逻辑，不符合向下查找子节点的需求
3. 外层嵌套子查询、group by去重属于冗余写法，正常无环树形结构不需要额外去重

原有错误SQL如下：

SELECT id FROM (  
        with recursive cat as ( 
            select * from table
             union all 
            select  table.* 
            from table  
            join cat on cat.id_parent = table.id
        ) 
        select  * from cat order by id 
    )
    as listado where id != '0'  group by id

注意：table是SQL保留关键字，实际使用请替换为真实业务表名，下文示例统一用your_tree_table指代业务表。

正确实现写法 #

PostgreSQL递归CTE分为两部分：锚点成员（定义递归起点）、递归成员（定义遍历规则），正确写法如下：

WITH RECURSIVE cat AS (
    -- 1. 锚点：指定递归起始节点，即传入的目标ID
    SELECT id, id_parent, name
    FROM your_tree_table
    WHERE id = 3 -- 此处替换为实际传入的ID参数，例如传入1则改为id=1
    UNION ALL
    -- 2. 递归规则：关联已查出的节点，向下查找所有直接子节点
    SELECT t.id, t.id_parent, t.name
    FROM your_tree_table t
    INNER JOIN cat c 
        ON t.id_parent = c.id -- 关联条件：子节点的父ID = 已查出节点的ID
)
-- 直接返回递归结果即可
SELECT id, id_parent, name 
FROM cat 
ORDER BY id;

效果验证 #

• 锚点条件设置为id=3时，返回结果为3、4、5，符合预期
• 锚点条件设置为id=1时，返回结果为1、3、4、5，符合预期

如果是在应用代码中传参，直接将锚点部分的固定ID替换为参数占位符即可，不需要在外层额外增加过滤条件。

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