Oracle 10g PL/SQL Connect By Prior 在同一行返回 Child 和 Parent
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18446721/
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
Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row
提问by RebeccaK375
Table1:
Child Parent a
Bob Chris 2
Chris Kate 1
Shane Lana 3
Nala Bob 4
Table2:
b Talent
1 'something'
2 'nothing'
3 'something'
4 'nothing'
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
This code returns rows of parents
此代码返回父行
Chris
克里斯
If the "Where" clause was not included, the code would return:
如果不包括“Where”子句，代码将返回：
Bob
Chris
Kate
Chris
Kate
Shane
Lana
etc
What I want to return is the following in a row, not in columns:
我要返回的是一行中的以下内容，而不是列中的内容：
Bob Chris
鲍勃克里斯
Where Chris is the one with a talent and is parent to Bob, so the code returns not only the parent, but also the child which originated the query for that parent, so in this code:
其中 Chris 是有才能的人，并且是 Bob 的父母，因此代码不仅返回父级，还返回发起该父级查询的子级，因此在此代码中：
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
I would have the Child chris with the talent and the previous child Bob who originated the search for chris, so lets say if Bob was child to Gala and Gala was child to Chris, I would still want to get only Bob and Chris in the result.
我将拥有具有天赋的 Child chris 和发起搜索 chris 的前一个孩子 Bob，所以假设如果 Bob 是 Gala 的孩子而 Gala 是 Chris 的孩子，我仍然希望在结果中只得到 Bob 和 Chris .
Conditions: I do not have the permission to create temporary or any sort of tables, so I cannot use any loop to do this unless I just do not know how to do it without a temporary table
条件：我没有创建临时表或任何类型表的权限，所以我不能使用任何循环来执行此操作，除非我不知道没有临时表如何操作
I do not know how I can return a child from before the "prior" statement and the new "child" which is actually the parent of the previous child.
我不知道如何从“先前”语句之前返回一个孩子，而新的“孩子”实际上是前一个孩子的父母。
采纳答案by ?? ??c Th?
You can use "HR" database to Test
您可以使用“HR”数据库进行测试
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id;
Employee Manager Pathlen Path
--------------- ------------ ---------- ----------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz King 3 /King/Kochhar/Higgins/Gietz
回答by ThinkJet
All you need is to use connect_by_rootoperator.
您所需要的只是使用connect_by_root运算符。
I'm not sure about query goal (e.g. to return or not to return a talented grandfather if parents are not talented) but usage of that operator may look like that:
我不确定查询目标（例如，如果父母没有才华，则返回或不返回有才华的祖父），但该运算符的用法可能如下所示：
select
originated_from_child,
found_ancestor,
is_ancestor_talented
from (
select
CONNECT_BY_ROOT relations.child originated_from_child,
relations.parent found_ancestor,
(
select count(1)
from table2
where
b = a
and
talent = 'something'
and
rownum = 1
) is_ancestor_talented
from
table1 relations
start with
relations.a in (
select talents.b
from table2 talents
where talents.talent = 'something')
connect by
prior relations.child = relations.parent
)
where
originated_from_child <> found_ancestor
and
is_ancestor_talented = 1
