您对第一级的查询（此处
depth与表格区别）应如下所示：
select l.name, h.child_id, 1 as depth from level ljoin level_hierarchy h on l.id = h.child_id where h.parent_id is null; name | child_id | depth ----------+----------+------- Level1_a | 1 | 1(1 row)
请注意的正确用法
is null（不要总是
=与进行比较）。
null``null
您可以将以上内容用作递归cte中的初始查询：
with recursive recursive_query as ( select l.name, h.child_id, 1 as depth from level l join level_hierarchy h on l.id = h.child_id where h.parent_id is nullunion all select l.name, h.child_id, depth + 1 from level l join level_hierarchy h on l.id = h.child_id join recursive_query r on h.parent_id = r.child_id)select *from recursive_query-- where depth = 2 name | child_id | depth ----------+----------+------- Level1_a | 1 | 1 Level2_b | 3 | 2 Level2_a | 19 | 2 Level3_a | 4 | 3 Level3_b | 5 | 3 Level4_a | 6 | 4 Level4_b | 7 | 4(7 rows)
