您尚未指定方言或列名，因此很难给出完美的示例…
-- Some random dataIF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROp TABLE #MyTableCREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAr(100))INSERT INTO #MyTable (ID, ParentID, Description) VALUES(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3(2, 1, 'Child'), -- Try changing the second value (1) to 2 (3, 2, 'SubChild')-- End random data;WITH RecursiveCTE (StartingID, Level, Parents, Loop, ID, ParentID, Description) AS( SELECT ID, 1, '|' + CAST(ID AS VARCHAr(MAX)) + '|', 0, * FROM #MyTable UNIOn ALL SELECt R.StartingID, R.Level + 1, R.Parents + CAST(MT.ID AS VARCHAr(MAX)) + '|', CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAr(MAX)) + '|%' THEN 1 ELSE 0 END, MT.* FROM #MyTable MT INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0)SELECt StartingID, Level, Parents, MAX(Loop) OVER (PARTITION BY StartingID) Loop, ID, ParentID, Description FROM RecursiveCTE ORDER BY StartingID, Level这样的事情将显示递归cte中是否存在循环。看一下专栏
Loop。照原样处理数据，没有循环。在注释中，有一些示例，说明了如何更改值以引起循环。
最后，递归cte
VARCHAr(MAX)以
|id1|id2|id3|（称为
Parents）形式创建一个id
，然后检查当前值
ID是否已在该“列表”中。如果是，它将
Loop列设置为1。在递归联接（
ABD R.Loop = 0）中检查此列。
结束查询使用a
MAX() OVER (PARTITION BY ...)将
Loop整个“块”链的列设置为1 。
稍微复杂一点，生成一个“更好”的报告：
-- Some random dataIF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROp TABLE #MyTableCREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAr(100))INSERT INTO #MyTable (ID, ParentID, Description) VALUES(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3(2, 1, 'Child'), -- Try changing the second value (1) to 2 (3, 3, 'SubChild')-- End random data-- The "terminal" childrens (that are elements that don't have childrens-- connected to them);WITH WithoutChildren AS( SELECT MT1.* FROM #MyTable MT1 WHERe NOT EXISTS (SELECt 1 FROM #MyTable MT2 WHERe MT1.ID != MT2.ID AND MT1.ID = MT2.ParentID)), RecursiveCTE (StartingID, Level, Parents, Descriptions, Loop, ParentID) AS( SELECt ID, -- StartingID 1, -- Level '|' + CAST(ID AS VARCHAr(MAX)) + '|', '|' + CAST(Description AS VARCHAr(MAX)) + '|', 0, -- Loop ParentID FROM WithoutChildren UNIOn ALL SELECt R.StartingID, -- StartingID R.Level + 1, -- Level R.Parents + CAST(MT.ID AS VARCHAr(MAX)) + '|', R.Descriptions + CAST(MT.Description AS VARCHAr(MAX)) + '|', CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAr(MAX)) + '|%' THEN 1 ELSE 0 END, MT.ParentID FROM #MyTable MT INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0)SELECt * FROM RecursiveCTE WHERe ParentID IS NULL OR Loop = 1该查询应返回所有“最后一个孩子”行以及完整的父链。如果没有循环，则为该列
Loop；
0如果有循环，
1则为该列。
