oracle SQL SELECT 在父 ID 组织的树中查找循环引用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5803570/
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 SELECT to find cyclic references in father-ID-organized tree?
提问by TheBlastOne
"Fun" with cyclic references:
循环引用的“乐趣”：
Suppose I have a table ELEMENTS which contain a hierarchy of elements, modeled by a father ID.
假设我有一个表 ELEMENTS，其中包含由父亲 ID 建模的元素层次结构。
The father ID field is null for the root.
根的父亲 ID 字段为空。
All other records have a non-null father id with the (autosequenced) primary key (ID) of the father element.
所有其他记录都有一个非空的父 id，带有ID父元素的（自动排序的）主键 ( )。
For example, using
例如，使用
SELECT *
FROM Elements
WHERE FATHER_ID not in (SELECT ID FROM Elements)
I can find all elements that have invalid father references (FATHER_IDis not a foreign key, let's assume that in this example).
我可以找到所有具有无效父引用的元素（FATHER_ID不是外键，让我们假设在这个例子中）。
But how can I find elements that do have a valid father reference BUT whose chain of father references does not end in the root?I think this can only happen for cyclic references, for example A is the father of B, but B is the father of A, too. Such a "subtree" is not linked to the root and thus is not part of the main tree. I want to find such subtrees.
但是，我如何才能找到确实具有有效父引用但其父引用链不以根结尾的元素？我认为这只会发生在循环引用中，例如 A 是 B 的父亲，但 B 也是 A 的父亲。这样的“子树”不链接到根，因此不是主树的一部分。我想找到这样的子树。
Of course, I am looking for a query that delivers those elements that lead to a cyclic reference no matter how long the chain of references may be.
当然，我正在寻找一个查询，无论引用链有多长，它都能提供导致循环引用的元素。
Is that possible in SQL, or do I need an iterative solution?
这在 SQL 中是可能的，还是我需要迭代解决方案？
采纳答案by Quassnoi
SELECT n.*, CONNECT_BY_ROOT(id), level
FROM elements n
START WITH
id IN
(
SELECT MIN(id)
FROM (
SELECT id, CONNECT_BY_ROOT(id) AS root
FROM elements
START WITH
id IN
(
SELECT id
FROM elements n
WHERE CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE
father_id = PRIOR id
)
CONNECT BY NOCYCLE
id = PRIOR father_id
)
GROUP BY
root
)
CONNECT BY NOCYCLE
id = PRIOR father_id
You may want to read this article:
您可能想阅读这篇文章：
