标题可能不是很清楚，所以让我们考虑这个例子(这不是我的代码，只是用这个例子来建模我的请求(
我有一个引用自身的表(如文件系统(
id | parent | name
----+----------+-------
1 | null | /
2 | 1 | home
3 | 2 | user
4 | 3 | bin
5 | 1 | usr
6 | 5 | local
是否可以发出sql请求，因此如果我选择：
1我将得到一个包含 2,3,4,5,6(因为这是根(的表，因此匹配：
- /家 /
- 首页/用户 /
- 首页/用户/垃圾桶
- /usr
- 等。。。
2我会得到一个包含 3,4 的表，因此匹配：
- 首页/用户 /
- 首页/用户/垃圾桶
等等
使用递归公用表表达式。始终从根开始，使用 id 数组来获取WHERE子句中给定id的路径。
对于id = 1：
with recursive cte(id, parent, name, ids) as (
select id, parent, name, array[id]
from my_table
where parent is null
union all
select t.id, t.parent, concat(c.name, t.name, '/'), ids || t.id
from cte c
join my_table t on c.id = t.parent
)
select id, name
from cte
where 1 = any(ids) and id <> 1
id | name
----+-----------------------
2 | /home/
5 | /usr/
6 | /usr/local/
3 | /home/user/
4 | /home/user/bin/
(5 rows)
对于id = 2：
with recursive cte(id, parent, name, ids) as (
select id, parent, name, array[id]
from my_table
where parent is null
union all
select t.id, t.parent, concat(c.name, t.name, '/'), ids || t.id
from cte c
join my_table t on c.id = t.parent
)
select id, name
from cte
where 2 = any(ids) and id <> 2
id | name
----+-----------------------
3 | /home/user/
4 | /home/user/bin/
(2 rows)
双向查询
这个问题真的很有趣。上面的查询效果很好，但效率低下，因为即使我们请求叶子，它也会解析所有树节点。更强大的解决方案是双向递归查询。内部查询从给定节点遍历到顶部，而外部查询从节点遍历到底部。
with recursive outer_query(id, parent, name) as (
with recursive inner_query(qid, id, parent, name) as (
select id, id, parent, name
from my_table
where id = 2 -- parameter
union all
select qid, t.id, t.parent, concat(t.name, '/', q.name)
from inner_query q
join my_table t on q.parent = t.id
)
select qid, null::int, right(name, -1)
from inner_query
where parent is null
union all
select t.id, t.parent, concat(q.name, '/', t.name)
from outer_query q
join my_table t on q.id = t.parent
)
select id, name
from outer_query
where id <> 2; -- parameter