sql-server-2008 - T-SQL 递归选择循环依赖

Question

我的数据库中有自依赖实体 (a)，它们是从另一个实体 (b) 引用的，并且给定一个特定的 (b) 实体，我需要获取所需的所有 (a) 实体。这些是多对多映射，所以我有一个单独的映射表。我认为带有 CTE 的递归 Select 是我最好的选择，但我遇到了一个问题:

This Fiddle说明了我的问题。如果某个用户引入了循环引用，我的递归选择就会停止。我一直在绞尽脑汁想办法解决这个问题。应该注意的是，虽然我在 fiddle 中引入了外键，但我使用的系统实际上并不尊重外键(与 DBA 的长期争论) - 我引入它们是为了使数据流更加清晰。

递归查询，对于那些不想点击 fiddle 的人:

WITH recur(objID) AS (
    SELECT usesObjID
        FROM #otherObj
        WHERE otherObjID = 1
    UNION ALL
    SELECT slaveObjID
        FROM #objMap
            INNER JOIN recur
                on #objMap.masterObjID = recur.objID
)SELECT objID from recur

有什么想法吗？这种设计尚未投入生产，因此我可以稍微更改架构，但我不想依赖于在插入时发现循环引用，除非它可以通过 T-SQL 完成。

Answer 1

可以设置 MAXRECURSION 的 CTE，这将防止无限循环，但您仍然会得到奇怪的结果，因为查询将继续在循环中运行，直到达到最大递归。

挑战在于循环涉及多个步骤，因此您不能仅通过检查子项的直接父项来确定您是否处于循环中。

处理此问题的一种方法是向 CTE 添加一个额外的列...这个新列，tree ，跟踪到目前为止包含的所有 ID，并在 ID 重复时停止。

WITH recur(objID, Tree) AS (
    SELECT 
        usesObjID, 
        CAST(',' + CAST(usesObjID AS VARCHAR) + ',' AS VARCHAR) AS Tree
    FROM otherObj
    WHERE otherObjID = 1
    UNION ALL
    SELECT 
        slaveObjID, 
        CAST(recur.Tree + CAST(slaveObjID AS VARCHAR) + ',' AS VARCHAR) AS Tree
    FROM objMap
        INNER JOIN recur
            ON objMap.masterObjID = recur.objID
    WHERE recur.Tree NOT LIKE '%,' + CAST(slaveObjID AS VARCHAR) + ',%'  
)SELECT objID from recur

Sql Fiddle Link