pg数据库递归查询

1、通过父级节点递归查询子节点
WITH RECURSIVE dict AS (
SELECT *
FROM xauth_department_info
WHERE parent_index_code= ‘370287000000’
union ALL
SELECT xauth_department_info.*
FROM xauth_department_info,
dict
WHERE xauth_department_info.parent_index_code = dict.index_code
)
SELECT index_code AS id, name as name, parent_index_code as parentId
FROM dict
ORDER BY parentId

2、通过子节点递归查询父级节点
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