TSQL CTE: как избежать кругового обхода?

Я написал очень простое выражение CTE, которое возвращает список всех групп, членом которых является пользователь.

Правила действуют следующим образом: пользователь может входить в несколько групп, а группы могут быть вложенными, так что группа может быть членом другой группы, и, кроме того, группы могут быть взаимными членами другой, поэтому группа A является членом группы. B и группа B также является членом группы A.

Мой CTE выглядит следующим образом и, очевидно, дает бесконечную рекурсию:

            ;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
            )

Я не могу найти простое решение, чтобы отследить те группы, которые я уже записал.

Я думал об использовании дополнительного параметра varchar в CTE для записи списка всех групп, которые я посетил, но использование varchar слишком грубо, не так ли?

Есть ли способ лучше?

 Haoest15 июн. 2012 г., 15:55
он не повторяется вечно, потому что он выдает ошибку после 100 рекурсивных вызовов. Прости мою формулировку.
 Bridge15 июн. 2012 г., 00:10
Вы уверены, что это навсегда? По умолчанию сервер равен 100 итерациям. Попробуйте прочитать наMAXRECURSION намек наMSDN.
 AakashM15 июн. 2012 г., 09:57
Сначала беспокойтесь об эффективности,then беспокоиться о грубости, если время позволяет :)

Ответы на вопрос(2)

используйте переменную таблицы дозорного. Функция будет перехватывать циклические ссылки независимо от того, сколько прыжков выполнено в круге, нет проблем с максимальной длиной nvarchar (max), легко модифицируемой для разных типов данных или даже для составных ключей, и вы можете назначить функцию проверочному ограничению.

CREATE FUNCTION [dbo].[AccountsCircular] (@AccountID UNIQUEIDENTIFIER)
RETURNS BIT 
AS
BEGIN
    DECLARE @NextAccountID UNIQUEIDENTIFIER = NULL;
    DECLARE @Sentinel TABLE
    (
        ID UNIQUEIDENTIFIER
    )
    INSERT INTO     @Sentinel
                ( [ID] )
    VALUES          ( @AccountID )
    SET @NextAccountID = @AccountID;

    WHILE @NextAccountID IS NOT NULL
    BEGIN
        SELECT  @NextAccountID = [ParentAccountID]
        FROM    [dbo].[Accounts]
        WHERE   [AccountID] = @NextAccountID;
        IF  EXISTS(SELECT 1 FROM @Sentinel WHERE ID = @NextAccountID)
            RETURN 1;
        INSERT INTO @Sentinel
                ( [ID] )
        VALUES      ( @NextAccountID )
    END
    RETURN 0;
END
Решение Вопроса

ующем примере у меня есть циклическое отношение от A, B, C, D, а затем обратно к A, и я избегаю цикла со строкой часового:

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
 Haoest17 июн. 2012 г., 23:03
приятно знать, спасибо.
 Haoest15 июн. 2012 г., 16:44
Мне нравится ваше решение, потому что оно работает. Но есть ли способ сделать это без строки дозорного? Я чувствую, что это неуклюже и дублирует, что мы должны добавить какой-то разделитель вокруг каждой записи дозорного, скажем, Sentinel = & lt; + CAST (родительский AS VARCHAR (MAX)) + "& gt;" Затем мы должны сделать то же самое в функции CharIndex (), потому что без разделителей могут быть ложные срабатывания. И что произойдет, если строка часового станет настолько большой, что она превысит длину varchar (max)?
 05 нояб. 2015 г., 07:54
Я думаю, что вы должны построить строку часового по-разному, чтобы избежать ложных срабатываний в общем случае (когда не используется CHAR (1)). CHARINDEX может найтиA вAB|C, но нет<A> в<AB><C>, Кроме того, если ID может содержать & lt; или & gt ;, вам также нужно правильно это кодировать. Конечно, это не проблема, если вы просто продолжаете использовать CHAR (1), но это нереальный случай. Независимо, отличная идея и +1 от меня!
 08 июн. 2015 г., 18:09
Большое спасибо, человек! Вы сделали мой день :)
 15 июн. 2012 г., 23:21
Я рад слышать, что это работает. Это что-то вроде взлома, и я, честно говоря, не могу думать о «чище» путь. Но имейте в виду, что часовой растет вдоль каждой рекурсивной ветви независимо, и поэтому будет увеличиваться только до максимальной глубины, умноженной на каждую строку, плюс разделитель. Ограничение VARCHAR (MAX) составляет 2 ГБ, а максимальная глубина может быть увеличена, если необходимо, максимум до 32767. Таким образом, маловероятно, что вы когда-либо переполните VARCHAR (MAX). У большинства рекурсивных работ может быть несколько тысяч деревьев, но глубина которых редко превышает 5 или около того. Таким образом, ваши сторожевые строки, как правило, остаются довольно маленькими.

Ваш ответ на вопрос