递归查询以从给定的ID中检索所有孩子

Question

我正在从Oracle迁移到Postgres，但我不知道该如何进行递归查询

是一个示例数据集：

ID	id_parent	名称
1	0	AA
2	0	AA
3 1	3	AA
4	AA 4 3	AA
5	3	AA 6 AA
6	2 AA 7 6	AA
6	AA	AA

对于ID = 3，我想获得

ID
3
4
5

for ID = 1

ID
1
3
4
5，

但我都有所有，但我不知道如何通过ID过滤：

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

Answer 1

无需使用子问题。您只需要仅修复递归查询，为 -

WITH RECURSIVE cat (id, id_parent) as 
    ( 
     SELECT id, id_parent
       FROM table
      WHERE id = 1
      UNION ALL
     SELECT d.id, d.id_parent
       FROM table d
       JOIN cat ON cat.id = d.id_parent
    ) 
SELECT id 
  FROM cat 
 ORDER BY id;