Oracle 中的递归
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4659803/
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
Recursion In Oracle
提问by myahya
I have the following table in an oracle:
我在 oracle 中有下表：
Parent(arg1, arg2)
and I want the transitive closure of the relation parent. That is, I want the following table
我想要关系父级的传递闭包。也就是说，我想要下表
Ancestor(arg1, arg2)
How is this possible in Oracle?
这在 Oracle 中怎么可能？
I am doing the following:
我正在做以下事情：
WITH Ancestor(arg1, arg2) AS (
SELECT p.arg1, p.arg2 from parent p
UNION
SELECT p.arg1 , a.arg2 from parent p, Ancestor a
WHERE p.arg2 = a.arg1
)
SELECT DISTINCT * FROM Ancestor;
I get the error
我收到错误
*Cause: column aliasing in WITH clause is not supported yet
*Action: specify aliasing in defintion subquery and retry
Error at Line: 1 Column: 20
How can I solve this without column aliasing?
如何在没有列别名的情况下解决此问题？
回答by Quassnoi
WITH Ancestor(arg1, arg2) AS
(
SELECT p.arg1, p.arg2
FROM parent p
WHERE arg2 NOT IN
(
SELECT arg1
FROM parent
)
UNION ALL
SELECT p.arg1, a.arg2
FROM Ancestor a
JOIN parent p
ON p.arg2 = a.arg1
)
SELECT *
FROM Ancestor
Oracleonly supports recursive CTEsince 11gRelease 2.
OracleCTE自11g第 2 版以来仅支持递归。
In earlier versions, use CONNECT BYclause:
在早期版本中，使用CONNECT BY子句：
SELECT arg1, CONNECT_BY_ROOT arg2
FROM parent
START WITH
arg2 NOT IN
(
SELECT arg1
FROM parent
)
CONNECT BY
arg2 = PRIOR arg1
回答by FrustratedWithFormsDesigner
Oracle allows recursive queries. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html
Oracle 允许递归查询。见：http: //www.adp-gmbh.ch/ora/sql/connect_by.html
Of course, these usually assume the hierarchical data is all in one table. Splitting it into separate tables makes things complicated.
当然，这些通常假设分层数据都在一张表中。将其拆分为单独的表会使事情变得复杂。
