本文介绍了Oracle 中的递归的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在 oracle 中有下表:
I have the following table in an oracle:
Parent(arg1, arg2)
并且我想要关系父级的传递闭包.也就是说，我想要下表
and I want the transitive closure of the relation parent. That is, I want the following table
Ancestor(arg1, arg2)
这在 Oracle 中怎么可能?
How is this possible in Oracle?
我正在做以下事情:
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;
我收到错误
*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?
推荐答案
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
Oracle 自 11g Release 2 起仅支持递归 CTE.
Oracle only supports recursive CTE since 11g Release 2.
在早期版本中，使用CONNECT BY 子句:
In earlier versions, use CONNECT BY clause:
SELECT arg1, CONNECT_BY_ROOT arg2
FROM parent
START WITH
arg2 NOT IN
(
SELECT arg1
FROM parent
)
CONNECT BY
arg2 = PRIOR arg1
这篇关于Oracle 中的递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！