postgreSql实现递归查询,从父到子,从子到父
使用场景:
有一个分类表,需要根据分类的id查询这个分类下面所有的子分类信息,以及子分类的子分类....
有一个分类表sort
id | name | parent_id |
---|---|---|
1 | 分类1 | root |
1-1 | 分类c | 1 |
2-1 | 分类b | 1-1 |
需要递归查询根据分类id为1查询出1-1,2-1的子分类和子子分类
查询父到子
WITH RECURSIVE dict AS (
SELECT *
FROM sort
WHERE id= '1'
union ALL
SELECT sort.*
FROM sort,
dict
WHERE sort.parent_id = dict.id --查询主表的父id等于虚拟表的id,不能反过来否则查询本身以及父节点
)
SELECT id AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name
查询结果:
id | name | parent_id |
---|---|---|
1 | 分类1 | root |
1-1 | 分类c | 1 |
2-1 | 分类d | 1-1 |
查询从子到父
WITH RECURSIVE dict AS (
SELECT *
FROM sort
WHERE id= '1-1'
union ALL
SELECT sort.*
FROM sort,
dict
WHERE sort.id = dict.parent_id --条件不同
)
SELECT id AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name
id | name | parent_id |
---|---|---|
1-1 | 分类c | 1 |
1 | 分类1 | root |
查询结果如上:查询顺序从子到父
-
sql中WITH xxxx AS () 是对一个查询子句做别名,同时数据库会对该子句生成临时表;
-
WITH RECURSIVE 则是一个递归的查询子句,他会把查询出来的结果再次代入到查询子句中继续查询
实战查询:从父到子
从子到父: