递归查询语法的 Oracle Connect By Prior
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19408622/
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 Connect By Prior for Recursive Query Syntax
提问by John Bustos
Suppose I had the following table in my oracle DB:
假设我的 oracle 数据库中有下表：
ID: Name: Parent_ID:
123 a 234
345 b 123
234 c 234
456 d 345
567 e 567
678 f 567
And what I would like to do is find, for each IDthe ULTIMATE parent ID(described as row, that when you go up, recursively, based upon Parent_IDthe row where you finally get that ID = Parent_ID).
而我希望做的是找到，每个ID的ULTIMATE parent ID（形容排，当你走了，递归，基于Parent_ID行，你终于拿到ID = Parent_ID）。
So, for example, 345's parent is 123 and 123's parent is 234 and 234's parent is 234 (meaning it is the top of the chain), therefore 345's ultimate parent is 234 - I hope this makes sense...
因此，例如，345 的父级是 123，123 的父级是 234，234 的父级是 234（意味着它是链的顶部），因此 345 的最终父级是 234 - 我希望这是有道理的......
So, my result should look as follows:
所以，我的结果应该如下所示：
ID: Name: Ult_Parent_ID: Ult_Parent_Name:
123 a 234 c
345 b 234 c
234 c 234 c
456 d 234 c
567 e 567 e
678 f 567 e
I just found out about Oracle Connect Bystatments today, so this is completely new to me, but I'm imagining my query would have to look SOMETHING as follows:
我Connect By今天才发现 Oracle statments，所以这对我来说是全新的，但我想我的查询必须看起来像下面这样：
SELECT ID, Name, Parent_ID as Ult_Parent_ID,
(SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name
FROM MyTable t1
CONNECT BY PRIOR Parent_ID = ID;
Now, like I said, this is my first stab at this kind of SQL - THIS DOES NOT WORK(I get the following error [1]: ORA-01436: CONNECT BY loop in user dataand it highlights the table name in the SQL editor), and I also don't know where / how to use the START WITHclause for this kind of query, but the logic of it seems correct to me.
现在，就像我说的，这是我第一次尝试这种 SQL -这不起作用（我收到以下错误[1]: ORA-01436: CONNECT BY loop in user data，它在 SQL 编辑器中突出显示了表名），而且我也不知道在哪里/如何将START WITH子句用于这种查询，但它的逻辑对我来说似乎是正确的。
Please help / help point me in the right direction!!!
请帮助/帮助指出我正确的方向！！！
Thanks!!!
谢谢！！！
采纳答案by Emmanuel
I think the CONNECT_BY_ROOT is what you need:
我认为 CONNECT_BY_ROOT 是你需要的：
select x.*, t2.name ultimate_name
from
(
select t.id, t.name, CONNECT_BY_ROOT parent_id ultimate_id
from toto t
start with t.id = t.parent_id
connect by nocycle prior id = parent_id
) x, toto t2
where x.ultimate_id = t2.id
;
This gives:
这给出：
456 d 234 c
345 b 234 c
123 a 234 c
234 c 234 c
678 f 567 e
567 e 567 e
回答by PM 77-1
Please try this one:
请试试这个：
SELECT ID, Name, Parent_ID as Ult_Parent_ID,
(SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name,
LEVEL
FROM MyTable t1
CONNECT BY NOCYCLE Parent_ID = PRIOR ID
START WITH Parent_ID = ID;
I believe that we have to use NOCYCLEbecause of how your roots are defined.
我相信我们必须使用，NOCYCLE因为您的根是如何定义的。
I added pseudo-column LEVELjust for illustration purposes. You do nothave to have it in your final query.
我添加了伪列LEVEL只是为了说明目的。您不必在最终查询中使用它。
SQL Fiddlewith your test data
SQL Fiddle处理您的测试数据
回答by Nicolas
A CONNECT BY will give you the immediate parent but to get the ultimate parent I would use a recursive subquery. (CONNECT_BY_ROOTas explained by Emmanuel also works)
CONNECT BY 将为您提供直接父级，但要获得最终父级，我将使用递归子查询。（CONNECT_BY_ROOT正如 Emmanuel 所解释的那样也有效）
WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl) as
(SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name, 0 lvl
FROM mytable
WHERE parent_id = id -- identifies a root
UNION ALL
SELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl + 1
FROM r join mytable m on m.parent_id = r.id -- joins child with parent
WHERE m.parent_id <> m.id -- to avoid cycles
)
SELECT * FROM r ;
The first part of the subquery fetches the roots and the second part connects the children. Parentis the immediate parent and ultimate_parent, the ultimate parent.
子查询的第一部分获取根，第二部分连接子查询。Parent是直接父级ultimate_parent， 是最终父级。
回答by Varun Vikram Singh
create table test_data (order_number number(10), line_id number(10), parent_line_id number (10));
创建表 test_data (order_number number(10), line_id number(10), parent_line_id number (10));
insert into test_data values (1000, 101, 100);
insert into test_data values (1000, 100, '');
insert into test_data values (3000, 301, 300);
insert into test_data values (3000, 300, '');
select * from test_data
select * from test_data
where order_number in (1000,3000)
start with parent_line_id is null
connect by prior line_id= parent_line_id
