Recursive CTE循环引用错误解决：查找原始Location的SQL实现

初始数据表格 #

待查询的ID集合 #

期望查询结果 #

问题现状 #

处理ID 2、4时脚本输出正常，但处理ID 8、10、11时因循环引用触发错误：

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

已知8是该循环链的根节点（链中最小ID），需要修改脚本避免错误并得到指定结果。现有可处理无循环数据的脚本如下：

DECLARE @IDs TABLE (
  ID INTEGER
  ,PreviousID INTEGER
  ,Location INTEGER
)

INSERT INTO @IDs
SELECT           2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,11,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235
UNION ALL SELECT 11,10,1237

Select * from @IDs


DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('2')
,('4')
--,('8')
--,('10')
--,('11')

;WITH q AS (
    SELECT 0 lvl,  ID, PreviousID,PreviousID LastId
        ,Location,Location as OriginalLocation
    FROM    @IDs
    where ID in (select OrderID from @ORDERID) 
    UNION ALL 
    SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
       ,q.Location,u.Location
    FROM    q
            INNER JOIN @IDs u ON u.ID = q.PreviousID
            --and q.ID <> u.PreviousID and q.PreviousID <> u.ID
)
select lvl, ID, coalesce(LastId,Id) OriginalId,Location,OriginalLocation 
from q
where PreviousId is null
order by id;

修改方案 #

核心思路是在递归过程中跟踪访问路径，同时结合“循环链根节点是链中最小ID”的规则终止递归，避免无限循环。修改后的脚本如下：

DECLARE @IDs TABLE (
  ID INTEGER
  ,PreviousID INTEGER
  ,Location INTEGER
)

INSERT INTO @IDs
SELECT           2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,11,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235
UNION ALL SELECT 11,10,1237

DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('2'),('4'),('8'),('10'),('11')

;WITH q AS (
    -- 初始查询：记录当前ID、访问路径、层级及节点信息
    SELECT 
        0 lvl, 
        ID, 
        PreviousID, 
        ID AS CurrentTrackId,
        CAST(ID AS VARCHAR(MAX)) AS VisitedIds,
        Location,
        Location AS OriginalLocation
    FROM @IDs
    WHERE ID IN (SELECT OrderID FROM @ORDERID)
    
    UNION ALL 
    
    SELECT 
        lvl + 1, 
        q.ID, 
        u.PreviousId,
        u.ID AS CurrentTrackId,
        q.VisitedIds + ',' + CAST(u.ID AS VARCHAR(MAX)),
        q.Location,
        -- 遇到更小的ID时更新原始Location
        CASE WHEN u.ID < q.CurrentTrackId THEN u.Location ELSE q.OriginalLocation END AS OriginalLocation
    FROM q
    INNER JOIN @IDs u ON u.ID = q.PreviousID
    -- 终止条件：未到链尾、未进入循环、当前ID不是链中最小ID
    WHERE 
        u.PreviousID IS NOT NULL
        AND CHARINDEX(',' + CAST(u.ID AS VARCHAR(MAX)) + ',', ',' + q.VisitedIds + ',') = 0
        AND u.ID > (SELECT MIN(ID) FROM @IDs WHERE ID IN (SELECT value FROM STRING_SPLIT(q.VisitedIds + ',' + CAST(u.ID AS VARCHAR(MAX)), ',')))
)
-- 提取最终结果：递归终止节点（链尾/最小ID节点/循环触发点）
SELECT 
    lvl, 
    ID, 
    COALESCE(
        (SELECT MIN(ID) FROM @IDs WHERE ID IN (SELECT value FROM STRING_SPLIT(q.VisitedIds + ',' + CAST(q.CurrentTrackId AS VARCHAR(MAX)), ','))),
        ID
    ) AS OriginalId,
    Location,
    OriginalLocation
FROM q
WHERE 
    q.PreviousID IS NULL 
    OR q.CurrentTrackId = (SELECT MIN(ID) FROM @IDs WHERE ID IN (SELECT value FROM STRING_SPLIT(q.VisitedIds + ',' + CAST(q.CurrentTrackId AS VARCHAR(MAX)), ',')))
ORDER BY ID;

方案说明 #

1. 跟踪访问路径：通过VisitedIds记录递归过程中已访问的ID，用CHARINDEX检测重复访问，避免无限递归。
2. 识别最小ID节点：利用已知规则，当递归到链中最小ID时终止，将该节点的Location作为原始Location。
3. 多条件终止递归：同时判断PreviousID是否为空（正常链终止）、是否进入循环、是否到达最小ID节点，确保递归及时停止。

内容的提问来源于stack exchange，提问作者T340B