2017.12.07 postgresql使用with recursive完成迭代查询

时间:2022-10-06 00:36:59

1.表结构

2017.12.07 postgresql使用with recursive完成迭代查询

 

2.需求

查询某条记录的所有父亲节点,或者所有孩子节点。

 

3.向上查询(查询所有父亲节点)

 注意,这里返回的记录包含自己。

sql如下:

1 WITH RECURSIVE res AS (
2   SELECT t1.* FROM t_code as t1  
3   WHERE t1.enable = 'true' and t1.id = '4'
4 
5   UNION 
6     SELECT t2.* from t_code as t2
7     INNER JOIN res as t3 ON t2.id = t3.parent_id
8     WHERE t2.enable = 'true'
9 )SELECT res.* from res

 

返回结果:

2017.12.07 postgresql使用with recursive完成迭代查询

 

4.向下查询(查询所有孩子节点)

注意,这里返回的记录包含自己。

和上面的sql只有一个差别:

1 INNER JOIN res as t3 ON t3.id = t2.parent_id

 

sql如下:

1 WITH RECURSIVE res AS (
2   SELECT t1.* FROM t_code as t1  
3   WHERE t1.enable = 'true' and t1.id = '1'
4 
5   UNION 
6         SELECT t2.* from t_code as t2
7     INNER JOIN res as t3 ON t3.id = t2.parent_id
8     WHERE t2.enable = 'true'
9 )SELECT res.* from res

 

返回结果:

2017.12.07 postgresql使用with recursive完成迭代查询

 

综上所述,可以将这段sql看做三个部分组成:(1)初始条件。(2)迭代条件。(3)将结果作为下一个迭代的初始条件。

其中迭代条件中,写作 INNER JOIN res as t3 ON t3.id = t2.parent_id 时,是表示上一次查询的结果(t3)是此时查询记录(t2)的父亲。即始终是用parent来查询children,为向下查询。