TSQL CTE：如何避免循环遍历？ [英] TSQL CTE: How to avoid circular traversal?

问题描述

我写了一个非常简单的CTE表达式，该表达式检索用户是其成员的所有组的列表。

I have written a very simple CTE expression that retrieves a list of all groups of which a user is a member.

规则是这样的，用户可以在多个组中，并且组可以嵌套，以便一个组可以是另一个组的成员，此外，组可以是另一个成员的共同成员，所以A组是B组的成员，B组也是A组的成员。

The rules goes like this, a user can be in multiple groups, and groups can be nested so that a group can be a member of another group, and furthermore, groups can be mutual member of another, so Group A is a member of Group B and Group B is also a member of Group A.

我的CTE显然是这样的产生无限递归：

My CTE goes like this and obviously it yields infinite recursion:

            ;WITH GetMembershipInfo(entityId) AS( -- entity can be a user or group
                SELECT k.ID as entityId FROM entities k WHERE k.id = @userId
                UNION ALL
                SELECT k.id FROM entities k 
                JOIN Xrelationships kc on kc.entityId = k.entityId
                JOIN GetMembershipInfo m on m.entityId = kc.ChildID
            )

找到一个简单的解决方案来回溯我已经记录的那些组。

I can't find an easy solution to back-track those groups that I have already recorded.

我当时想在CTE中使用一个额外的varchar参数来记录我访问过的所有组的列表，但是使用varchar太粗糙了，不是

I was thinking of using an additional varchar parameter in the CTE to record a list of all groups that I have visited, but using varchar is just too crude, isn't it?

有更好的方法吗？

推荐答案

您需要在您的递归中积累一个前哨字符串。在下面的示例中，我有一个从A，B，C，D到然后回到A的循环关系，并且避免了前哨字符串的循环：

You need to accumulate a sentinel string within your recursion. In the following example I have a circular relationship from A,B,C,D, and then back to A, and I avoid a loop with the sentinel string:

DECLARE @MyTable TABLE(Parent CHAR(1), Child CHAR(1));

INSERT @MyTable VALUES('A', 'B');
INSERT @MyTable VALUES('B', 'C');
INSERT @MyTable VALUES('C', 'D');
INSERT @MyTable VALUES('D', 'A');

; WITH CTE (Parent, Child, Sentinel) AS (
    SELECT  Parent, Child, Sentinel = CAST(Parent AS VARCHAR(MAX))
    FROM    @MyTable
    WHERE   Parent = 'A'
    UNION ALL
    SELECT  CTE.Child, t.Child, Sentinel + '|' + CTE.Child
    FROM    CTE
    JOIN    @MyTable t ON t.Parent = CTE.Child
    WHERE   CHARINDEX(CTE.Child,Sentinel)=0
)
SELECT * FROM CTE;

结果：

Parent Child Sentinel
------ ----- --------
A      B     A
B      C     A|B
C      D     A|B|C
D      A     A|B|C|D

这篇关于TSQL CTE：如何避免循环遍历？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！