sql-server – 如何在表引用自身时编写查找所有循环引用的查询？

我有以下架构(名称已更改),我无法更改：

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

也就是说,每条记录都是另一条记录的子记录.如果记录的ParentId等于其Id,则该记录被视为根节点.

我想运行查询,它将找到所有循环引用.例如,有了数据

INSERT INTO MyTable (Id,ParentId) VALUES
    (0,0),(1,(2,4),(3,2),(4,3);

查询应该返回

Id | Cycle
2  | 2 < 4 < 3 < 2
3  | 3 < 2 < 4 < 3
4  | 4 < 3 < 2 < 4

我为SQL Server 2008 R2编写了以下查询,我想知道是否可以改进此查询：

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (Id INT,HasParentalCycle BIT,Cycle VARCHAR(MAX));

DECLARE @i INT,@j INT,@flag BIT,@isRoot BIT,@ids VARCHAR(MAX);

DECLARE MyCursor CURSOR FAST_FORWARD FOR
    SELECT Id
    FROM MyTable;

OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @i;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF OBJECT_ID(N'tempdb..#Parents') IS NOT NULL DROP TABLE #Parents;
    CREATE TABLE #Parents (Id INT);

    SET @ids = NULL;
    SET @isRoot = 0;
    SET @flag = 0;
    SET @j = @i;
    INSERT INTO #Parents (Id) VALUES (@j);

    WHILE (1=1)
    BEGIN
        SELECT
            @j = ParentId,@isRoot = CASE WHEN ParentId = Id THEN 1 ELSE 0 END
        FROM MyTable
        WHERE Id = @j;

        IF (@isRoot = 1)
        BEGIN
            SET @flag = 0;
            BREAK;
        END        

        IF EXISTS (SELECT 1 FROM #Parents WHERE Id = @j)
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
            SET @flag = 1;
            SELECT @ids = COALESCE(@ids + ' < ','') + CAST(Id AS VARCHAR) FROM #Parents;
            BREAK;
        END
        ELSE
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
        END        
    END

    INSERT INTO #Results (Id,HasParentalCycle,Cycle) VALUES (@i,@flag,@ids);

    FETCH NEXT FROM MyCursor INTO @i;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;

SELECT Id,Cycle
FROM #Results
WHERE HasParentalCycle = 1;

解决方法

这需要递归CTE：

WITH FindRoot AS
(
    SELECT Id,ParentId,CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id,P.ParentId,C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0;

在这里看到它：SQL Fiddle

更新：

添加距离以排除所有自我循环(请参阅ypercube的评论)：

WITH FindRoot AS
(
    SELECT Id,CAST(Id AS NVARCHAR(MAX)) Path,0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id,C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)),C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0
  AND R.Distance > 0;

SQL Fiddle

您应该使用哪一个取决于您的要求.