基于邻接表的递归查询 [英] Recursive query for hirarchical data based on adjacency list

问题描述

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

Learing SQL, and have a bit of a problem. I have 2 tables level and 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    | 

现在我需要的是一个查询，它将返回表中的所有条目级别，该级别基于标记我要从中获取条目的级别层次结构的参数。

Now what I need, is a query that will return all entries from table level from every hirarchy level based on parameter that marks what level hierarchy level I want to get entries from.

获取 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 ，依此类推。

are just the ones that have a parent and the parent of their parent is NULL and so on. This is where I suspect that recursion comes in.

有没有人可以指导这种方法？

Is there anyone who can guide thorugh it?

推荐答案

您对第一级的查询（此处为深度以区别于表）应如下所示：

Your query for the first level (here depth to distinguish from the table) should look like this:

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)

请注意的正确用法是null （不要使用 = 与 null 进行比较，因为它总是给出 null ）。

Note the proper use of is null (do not use = to compare with null as it always gives null).

您可以将以上内容用作递归cte中的初始查询：

You can use the above as an initial query in a recursive 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)    

这篇关于基于邻接表的递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！