我有以下架构(名称已更改),我无法更改：
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;
您应该使用哪一个取决于您的要求.
