PostgreSQL递归查询上下级节点树（从子到父、从父到子）

• • 字典表结构（dictionary）
  • 字典表数据
  • 开始递归查询
  • • 向下递归(从父到子)
    • 向上递归(从子到父)
  • 结语

场景：
数据库中的数据存在父子关系(单继承,每一条记录只有一个父记录). 如果要查询一条记录以及他的所有子记录,或者要查询一条记录以及他的所有父记录.那么递归查询就再合适不过了.可以简化复杂的SQL语句。
现在数据库有一张dictionary表,用于存放业务相关字典项

字典表结构（dictionary）

id，name，parent_id

字典表数据

id	name	parentId
1	字典1	NULL
1-1	字典1-1	1
1-2	字典1-2	1
2	字典2	NULL
2-1	字典2-1	2

开始递归查询

向下递归(从父到子)

WITH RECURSIVE dict AS (
     SELECT *
     FROM dictionary
     WHERE id= '1'
     union ALL
     SELECT dictionary.*
     FROM dictionary,
          dict
     WHERE dictionary.parent_id = dict.id
)
SELECT id AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

查询结果

id	name	parentId
1	字典1	NULL
1-1	字典1-1	1
1-2	字典1-2	1

向上递归(从子到父)

WITH RECURSIVE dict AS (
     SELECT *
     FROM dictionary
     WHERE id= '2-1'
     union ALL
     SELECT dictionary.*
     FROM dictionary,
          dict
     WHERE dictionary.id = dict.parent_id
)
SELECT id AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

查询结果

id	name	parentId
2	字典2	NULL
2-1	字典2-1	2

结语

1. sql中WITH xxxx AS () 是对一个查询子句做别名，同时数据库会对该子句生成临时表；

2. WITH RECURSIVE 则是一个递归的查询子句，他会把查询出来的结果再次代入到查询子句中继续查询

​

//todo 此种方式都是查询一棵树，如果结果list中有多棵截断的树，需要递归查出来最顶层的parentId节点集合（就是找出 父节点对象不存在的就是顶层），然后循环这个集合挨个拼成这些树，并且再放进一个tree集合中，从而形成一个多树的json，但是此种方法有一个很大的问题：效果上看导致小树的顶层位置都从最前面开始了，与实际不否，实际中应该是错开的几棵树，综述实际开发中不会有这样的结果list。