SQL SELECT在父亲ID组织的树中查找循环引用? [英] SQL SELECT to find cyclic references in father-ID-organized tree?

问题描述

带有循环引用的有趣":

"Fun" with cyclic references:

假设我有一个表格ELEMENTS，其中包含由父亲ID建模的元素层次结构.

Suppose I have a table ELEMENTS which contain a hierarchy of elements, modeled by a father ID.

父亲ID字段的根字段为空.

The father ID field is null for the root.

所有其他记录的父亲ID为非空，其父亲元素具有(自动排序的)主键(ID).

All other records have a non-null father id with the (autosequenced) primary key (ID) of the father element.

例如，使用

SELECT *
FROM Elements
WHERE FATHER_ID not in (SELECT ID FROM Elements)

我可以找到所有具有无效父亲引用的元素(FATHER_ID不是外键，在此示例中假设).

I can find all elements that have invalid father references (FATHER_ID is not a foreign key, let's assume that in this example).

但是如何找到具有有效父引用BUT且父引用链不以根结尾的元素?我认为这仅适用于循环引用，例如A是B的父亲，但B也是A的父亲.这样的子树"没有链接到根，因此也不是主树的一部分. 我想找到这样的子树.

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.

当然，我正在寻找一个查询，该查询将提供那些导致循环引用的元素，无论引用链有多长.

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.

在SQL中是否可行，还是需要迭代解决方案?

推荐答案

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:

• 在树中查找循环

这篇关于SQL SELECT在父亲ID组织的树中查找循环引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！