基于邻接列表的层次数据递归查询

Question

学习SQL，并遇到一些问题。我有两个表level和level_hierarchy

|name        | id |     |parent_id | child_id|
-------------------     ---------------------
| Level1_a   | 1  |     | NULL     |    1    |
| Level2_a   | 19 |     | 1        |    19   |
| Level2_b   | 3  |     | 1        |    3    |
| Level3_a   | 4  |     | 3        |    4    |
| Level3_b   | 5  |     | 3        |    5    | 
| Level4_a   | 6  |     | 5        |    6    | 
| Level4_b   | 7  |     | 5        |    7    | 

现在，我需要的是一个查询，它将根据标记我希望从哪个级别层次结构级别获取条目的参数，从每个层次结构级别返回表level中的所有条目。

获取Level1条目非常容易。

SELECT name FROM level INNER JOIN level_hierarchy ON level.id = 
level_hierarchy.child_id WHERE level_hierarchy.parent_id=NULL

Level2条目：

Level2_a
Level2_b

只是那些有父级的，它们的父级是NULL等等。这就是我怀疑递归的原因。

有谁能指导你通过它吗？

Answer 1

您的第一级查询(此处为depth以区别于表(应该如下所示：

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 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 null
union 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)    

Answer 2

好问题，递归是SQL中的一个难题，它的实现因引擎而异。感谢您使用PostgreSQL标记您的帖子。PostgreSQL有一些关于这个主题的优秀文档。

WITH RECURSIVE rec_lh(child_id, parent_id) AS (
SELECT child_id, parent_id FROM level_hierarchy
UNION ALL
SELECT lh.child_id, lh.parent_id
FROM rec_lh rlh INNER JOIN level_hierarchy lh
ON lh.parent_id = rlh.child_id
)
SELECT DISTINCT level.name, child_id 
FROM rec_lh INNER JOIN level
ON rec_lh.parent_id = level.id
ORDER BY level.name ASC;

另请参阅：

PostgreSQL中的递归查询。选择*