自引用表 (Oracle) 上的 SQL 递归查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2319284/
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
SQL recursive query on self referencing table (Oracle)
提问by Maxim Veksler
Lets assume I have this sample data:
让我们假设我有这个样本数据：
| Name | ID | PARENT_ID |
-----------------------------
| a1 | 1 | null |
| b2 | 2 | null |
| c3 | 3 | null |
| a1.d4 | 4 | 1 |
| a1.e5 | 5 | 1 |
| a1.d4.f6 | 6 | 4 |
| a1.d4.g7 | 7 | 4 |
| a1.e5.h8 | 8 | 5 |
| a2.i9 | 9 | 2 |
| a2.i9.j10| 10 | 9 |
I would like to select all records start from accountId = 1, so the expected result would be:
我想选择从 accountId = 1 开始的所有记录，所以预期的结果是：
| Name | ID | PARENT_NAME | PARENT_ID |
-------------------------------------------
| a1 | 1 | null | null |
| a1.d4 | 4 | a1 | 1 |
| a1.e5 | 5 | a1 | 1 |
| a1.d4.f6 | 6 | a1.d4 | 4 |
| a1.d4.g7 | 7 | a1.d4 | 4 |
| a1.e5.h8 | 8 | a1.e5 | 5 |
I am currently able to make the recursive select, but then I can't access the data from the parent reference, hence I can't return parent_name. The code I'm using is (adapted to the simplistic example):
我目前能够进行递归选择，但是我无法从父引用访问数据，因此我无法返回 parent_name。我使用的代码是（适用于简单的例子）：
SELECT id, parent_id, name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id
What SQL should I be using to the mentioned above retrieval?
我应该使用什么 SQL 来进行上述检索？
Additional key words for future seekers: SQL to select hierarchical data represented by parent keys in same table
未来寻求者的额外关键词：SQL 选择由同一表中的父键表示的分层数据
回答by OMG Ponies
Use:
用：
SELECT t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
FROM tbl t1
LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1
CONNECT BY PRIOR t1.id = t1.parent_id
回答by ari
What about using PRIOR,
使用 PRIOR 怎么样，
so
所以
SELECT id, parent_id, PRIOR name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id`or if you want to get the root name
或者如果你想获得根名称
SELECT id, parent_id, CONNECT_BY_ROOT name
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id回答by PaulMurrayCbr
Using the new nested query syntax
使用新的嵌套查询语法
with q(name, id, parent_id, parent_name) as (
select
t1.name, t1.id,
null as parent_id, null as parent_name
from t1
where t1.id = 1
union all
select
t1.name, t1.id,
q.id as parent_id, q.name as parent_name
from t1, q
where t1.parent_id = q.id
)
select * from q
回答by Samuel
Do you want to do this?
你想这样做吗？
SELECT id, parent_id, name,
(select Name from tbl where id = t.parent_id) parent_name
FROM tbl t start with id = 1 CONNECT BY PRIOR id = parent_id
EditAnother option based on OMG's one (but I think that will perform equally):
编辑基于 OMG 的另一个选项（但我认为这将同样执行）：
select
t1.id,
t1.parent_id,
t1.name,
t2.name AS parent_name,
t2.id AS parent_id
from
(select id, parent_id, name
from tbl
start with id = 1
connect by prior id = parent_id) t1
left join
tbl t2 on t2.id = t1.parent_id
回答by Allan
It's a little on the cumbersome side, but I believe this should work (without the extra join). This assumes that you can choose a character that will never appear in the field in question, to act as a separator.
这有点麻烦，但我相信这应该可行（没有额外的连接）。这假设您可以选择一个永远不会出现在相关字段中的字符作为分隔符。
You can do it without nesting the select, but I find this a little cleaner that having four references to SYS_CONNECT_BY_PATH.
您可以在不嵌套选择的情况下执行此操作，但我发现这对 SYS_CONNECT_BY_PATH 有四个引用更清晰。
select id,
parent_id,
case
when lvl <> 1
then substr(name_path,
instr(name_path,'|',1,lvl-1)+1,
instr(name_path,'|',1,lvl)
-instr(name_path,'|',1,lvl-1)-1)
end as name
from (
SELECT id, parent_id, sys_connect_by_path(name,'|') as name_path, level as lvl
FROM tbl
START WITH id = 1
CONNECT BY PRIOR id = parent_id)
