PostgreSQL递归查询：获取自关联表中符合条件的首个后代

我有一张带自关联外键的表，需要获取符合条件的首个子节点或后代节点，但不知如何实现。

表结构如下：

需求：针对id为1（类型为union）的节点，获取所有直接子节点或首个后代节点，跳过union与首个后代之间的所有group节点。预期结果如下：

例如id=4是通过id=2、3的group节点关联到union，id=5是直接关联到union。我尝试编写了递归查询，但未得到预期结果：

with recursive cte AS (
  select b.id, p.type_id
  from my_table b 
  join my_table p on p.id = b.parent_id
  where b.id = 1

  union
 
  select c.id, cte.type_id      
  from my_table c
  join cte on cte.id = c.parent_id
  where c.parent_id = 1 or cte.type_id = 'group'
 )

解决方案 #

正确的递归查询需要调整逻辑：从union节点的直接子节点开始，递归遍历子节点，仅当当前节点是group类型时继续向下，直到找到非group类型的节点，最终过滤出这些有效节点即可。

对应的SQL代码：

WITH RECURSIVE cte AS (
    -- 初始化：获取union节点（id=1）的所有直接子节点
    SELECT 
        id, 
        parent_id, 
        type
    FROM my_table
    WHERE parent_id = 1

    UNION ALL

    -- 递归：仅对group类型的节点继续向下遍历其子节点
    SELECT 
        m.id, 
        m.parent_id, 
        m.type
    FROM my_table m
    JOIN cte ON m.parent_id = cte.id
    WHERE cte.type = 'group'
)
-- 过滤掉所有group类型的节点，得到目标结果
SELECT id, type FROM cte WHERE type != 'group';

逻辑说明 #

1. 初始化阶段：先拿到union节点的所有直接子节点（id=2、5、7）。
2. 递归阶段：如果当前节点是group类型，就继续获取它的子节点——比如id=2是group，递归获取id=3；id=3也是group，继续递归获取id=4。
3. 最终过滤：从CTE的所有结果中剔除group类型的节点，剩下的就是跳过中间group后的首个有效后代节点，完全符合预期。

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