问题描述
给定一个简单的(id, description)表t1，比如
Given a simple (id, description) table t1, such as
id description
-- -----------
1 Alice
2 Bob
3 Carol
4 David
5 Erica
6 Fred
还有一个父子关系表t2，比如
And a parent-child relationship table t2, such as
parent child
------ -----
1 2
1 3
4 5
5 6
Oracle 提供了一种将其作为具有一些自定义语法扩展的树来遍历的方法:
Oracle offers a way of traversing this as a tree with some custom syntax extensions:
select parent, child, sys_connect_by_path(child, '/') as "path"
from t2
connect by prior parent = child
确切的语法并不重要，我可能在上面犯了一个错误.这重要的是，上面会产生一些看起来像
The exact syntax is not important, and I've probably made a mistake in the above. Theimportant thing is that the above will produce something that looks like
parent child path
------ ----- ----
1 2 /1/2
1 3 /1/3
4 5 /4/5
4 6 /4/5/6
5 6 /5/6
我的问题是:是否可以在 sys_connect_by_path() 中加入另一个表，例如上面的 t1 表，以生成如下内容:
My question is this: is it possible to join another table within the sys_connect_by_path(), such as the t1 table above, to produce something like:
parent child path
------ ----- ----
1 2 /Alice/Bob
1 3 /Alice/Carol
... and so on...
推荐答案
在您的查询中，将 T2 替换为连接 T1 和 T2 的子查询，并返回父、子和子描述.然后在 sys_connect_by_path 函数中，从您的子查询中引用子描述.
In your query, replace T2 with a subquery that joins T1 and T2, and returns parent, child and child description. Then in the sys_connect_by_path function, reference the child description from your subquery.
这篇关于在 oracle 树查询中加入其他表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！