如何执行此递归查询以获取每个id的子id

Question

考虑下表：

	父ID
1	空
2	1
3	2
4	3

获取所有行，在跟踪级别的同时向下递归，然后使用array_agg()合并，使用array_remove删除行的"自我；id。

with recursive walk as (
select id, parentid, id as start_id, 0 as level
from tblname
union all
select c.id, c.parentid, p.start_id, p.level + 1 as level
from walk p
join tblname c on c.parentid = p.id
)
select start_id, array_remove(array_agg(id order by level), start_id) as children 
from walk
group by start_id; 

nullif()只是为了匹配您的规格

db<gt；小提琴这里