PostgreSQL递归查询（从子到父、从父到子）

开发中需要这种场景的实现，需要保证编码唯一；

如果查询出来有重复值，说明编码重复了

1.通过父节点唯一标识查询所有子节点（从父到子）

id	code	parent_code	name	sort
5	102000	0	规章制度	1
6	102001	102000	银行制度-测试数据	1
7	102002	102000	国家制度规范-测试数据	1

WITH RECURSIVE dict AS (
     SELECT *
     FROM category
     WHERE code= '102000'
     union ALL
     SELECT ca.*
     FROM category ca,
          dict
     WHERE ca.parent_code = dict.code
)
SELECT id AS id, name as name,code, parent_code 
FROM dict
ORDER BY id

得到的结果：

2.通过子节点，获取父节点（从子到父）

WITH RECURSIVE dict AS (
     SELECT *
     FROM category
     WHERE code= '102004'
     union ALL
     SELECT ca.*
     FROM category ca,
          dict
     WHERE ca.code = dict.parent_code
)
SELECT id AS id,code, name as name, parent_code 
FROM dict
ORDER BY id

 

得到的编码为“102004”的所有上级节点：