标题可能不太清楚，所以让我们考虑这个示例（这不是我的代码，仅以这个示例为我的请求建模）
我有一张桌子引用自身的文件（例如文件系统）
id |父母|名称 ---- + ---------- + ------- 1 |空| / 2 | 1 |家 3 | 2 |用户 4 | 3 | bin 5 | 1 | usr 6 | 5 |本地是否可以发出sql请求，所以如果我选择：
1 我将得到一个包含2,3,4,5,6（因为这是根）的表，因此匹配：
- / home
- / home / user
- / home /用户/ bin
- / usr
- 等...
2 我将得到一个包含3,4的表，这样匹配：
- / home / user
- / home / user / bin
依此类推
解决方案使用递归公用表表达式。始终从根开始，使用ID数组在 WHERE 子句中获取给定 id 的路径。 / p>
对于 id = 1 ：
具有递归cte（id，父代，名称，id）as（ select id，父代，名称，数组[id] 来自my_table 其中parent为空 union all 从cte c 中选择t.id，t.parent，concat（c.name，t.name，'/'），id || t.id 加入my_table t on c.id = t.parent ）选择id，从cte 中命名其中1 = any（ids）和id<> 1 id |名称 ---- + ----------------------- 2 | / home / 5 | / usr / 6 | / usr / local / 3 | / home / user / 4 | / home / user / bin / （5行）对于 id = 2 ：
，其中递归cte（id，parent，name，ids）为（ select id，parent，name，array [id] from my_table 其中parent为空 union all select t.id，t.parent，concat（ c.name，t.name，'/'），id || t.id 来自cte c 加入my_table t on c.id = t.parent ）选择id，从cte 中命名，其中2 = any（ids），id<> 2 id |名称 ---- + ----------------------- 3 | / home / user / 4 | / home / user / bin / （2行）双向查询
这个问题真的很有趣。上面的查询效果很好，但是效率很低，因为即使我们要叶子，它也会解析所有树节点。更为强大的解决方案是双向递归查询。内部查询从给定的节点到顶部，而外部查询从该节点到底部。
具有递归external_query（ id，parent，name）as（ with recursive inner_query（qid，id，parent，name）as（ select id，id，parent，name，my_table 中的其中id = 2-参数并集所有从内部查询中选择qid，t.id，t.parent，concat（t.name，'/'，q.name） q 在q.parent = t.id ）上加入my_table t从内部查询中选择qid，null :: int，right（name，-1）其中parent为null union all 从外部查询q中选择t.id，t.parent，concat（q.name，'/'，t.name） q 在q上加入my_table t。 id = t.parent ）选择id，从external_query 中命名，其中id<> 2; -参数
the title may not be very clear so let's consider this example (this is not my code, just taking this example to model my request)
I have a table that references itself (like a filesystem)
id | parent | name ----+----------+------- 1 | null | / 2 | 1 | home 3 | 2 | user 4 | 3 | bin 5 | 1 | usr 6 | 5 | localIs it possible to make a sql request so if I choose :
1 I will get a table containing 2,3,4,5,6 (because this is the root) so matching :
- /home
- /home/user
- /home/user/bin
- /usr
- etc...
2 I will get a table containing 3,4 so matching :
- /home/user
- /home/user/bin
and so on
解决方案Use recursive common table expression. Always starting from the root, use an array of ids to get paths for a given id in the WHERE clause.
For 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)For 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)Bidirectional query
The question is really interesting. The above query works well but is inefficient as it parses all tree nodes even when we're asking for a leaf. The more powerful solution is a bidirectional recursive query. The inner query walks from a given node to top, while the outer one goes from the node to bottom.
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
更多推荐
如何在引用自身的postgresql表中从给定ID递归获取所有ID？
发布评论