SQL SELECT 在父亲 ID 组织树中查找循环引用？

发布于 2024-11-03 14:30:02 字数 565 浏览 5 评论 0原文

循环引用的“乐趣”：

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

对于根来说，父亲 ID 字段为空。

所有其他记录都有一个非空父 id，其中包含父元素的（自动排序的）主键 (ID)。

例如，使用

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

I 可以找到具有无效父引用的所有元素（FATHER_ID 不是外键，我们假设在本例中是这样）。

但是我怎样才能找到确实具有有效父引用但其父引用链不以根结尾的元素？我认为这只会发生在循环引用中，例如 A 是B，但B也是A的父亲。这样的“子树”不链接到根，因此不是主树的一部分。 我想找到这样的子树。

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

这在 SQL 中可行吗？还是我需要迭代解决方案？

原文

"Fun" with cyclic references:

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

The father ID field is null for the root.

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

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_ID is not a foreign key, let's assume that in this example).

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.

Is that possible in SQL, or do I need an iterative solution?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甲如呢乙后呢 2024-11-10 14:30:02

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

您可能想阅读这篇文章：

查找循环一棵树

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: