在 oracle 树查询中加入其他表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/117512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining other tables in oracle tree queries
提问by dland
Given a simple (id, description) table t1, such as
给定一个简单的 (id, description) 表 t1，例如
id description
-- -----------
1 Alice
2 Bob
3 Carol
4 David
5 Erica
6 Fred
And a parent-child relationship table t2, such as
还有一个父子关系表t2，比如
parent child
------ -----
1 2
1 3
4 5
5 6
Oracle offers a way of traversing this as a tree with some custom syntax extensions:
Oracle 提供了一种将其作为具有一些自定义语法扩展的树来遍历的方法：
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. The important 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
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:
我的问题是：是否可以在 sys_connect_by_path() 中加入另一个表，例如上面的 t1 表，以生成如下内容：
parent child path
------ ----- ----
1 2 /Alice/Bob
1 3 /Alice/Carol
... and so on...
采纳答案by Mike McAllister
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.
在您的查询中，将 T2 替换为连接 T1 和 T2 的子查询，并返回父、子和子描述。然后在 sys_connect_by_path 函数中，从您的子查询中引用子描述。
回答by dland
Based on Mike McAllister's idea, the following uses a derived table to achieve the desired result:
基于 Mike McAllister 的想法，下面使用一个派生表来达到预期的效果：
select
T.PARENT
,T.CHILD
,sys_connect_by_path(T.CDESC, '/')
from
(
select
t2.parent as PARENT
,t2.child as CHILD
,t1.description as CDESC
from
t1, t2
where
t2.child = t1.id
) T
where
level > 1 and connect_by_isleaf = 1
connect by prior
T.CHILD = T.PARENT
In my problem, all the parents are anchored under a "super-parent" root, which means that the paths can be fully described with SYS_CONNECT_BY_PATH, thereby obviating the need for cagcowboy's technique of concatenating the parent with the path.
在我的问题中，所有父项都锚定在“超级父级”根下，这意味着可以使用 SYS_CONNECT_BY_PATH 完全描述路径，从而不需要 cagcowboy 将父级与路径连接的技术。
回答by cagcowboy
SELECT parent, child, parents.description||sys_connect_by_path(childs.description, '/') AS "path"
FROM T1 parents, T1 childs, T2
WHERE T2.parent = parents.id
AND T2.child = childs.id
CONNECT BY PRIOR parent = child
