SQL Server递归查询中如何避免重复记录与无限循环？

问题背景 #

现有人员关系数据结构中，单条记录对应一个人员，人员间存在多种单向关系。使用递归CTE查询指定节点后代时存在两个核心问题：

• 结果出现重复记录：同一节点可通过多条路径被多次遍历
• 闭环触发无限循环：当关系形成回路时，递归会无限执行导致查询失败

使用DISTINCT/GROUP BY去重、设置MAXRECURSION限制深度的方案会产生不必要的计算开销，需要从递归过程本身入手，跳过已访问节点来从根源解决问题。

解决方案核心思路 #

在递归CTE中维护一个已访问节点的集合，每次递归迭代时，仅处理目标节点未被访问过的关系。通过在递归过程中传递并更新这个集合，确保每个节点只被遍历一次，彻底避免重复和循环。

修改后的递归CTE代码 #

WITH RecursivePeople(RootPeopleId, SourcePeopleId, TargetPeopleId, TreeLevel, VisitedNodes) AS
(
    -- 初始成员：每个人员作为根节点，已访问集合仅包含自身
    SELECT 
        Id AS RootPeopleId,
        CAST(NULL AS INT) AS SourcePeopleId,
        Id AS TargetPeopleId,
        0 AS TreeLevel,
        CONCAT(',', CAST(Id AS VARCHAR(10)), ',') AS VisitedNodes
    FROM People

    UNION ALL

    -- 递归成员：仅关联目标节点未在已访问集合中的关系
    SELECT 
        rp.RootPeopleId,
        pr.SourcePeopleId,
        pr.TargetPeopleId,
        rp.TreeLevel + 1,
        CONCAT(rp.VisitedNodes, CAST(pr.TargetPeopleId AS VARCHAR(10)), ',')
    FROM PeopleRelationships pr
    JOIN RecursivePeople rp ON rp.TargetPeopleId = pr.SourcePeopleId
    -- 关键判断：目标节点不在已访问集合中
    WHERE CHARINDEX(CONCAT(',', CAST(pr.TargetPeopleId AS VARCHAR(10)), ','), rp.VisitedNodes) = 0
)
SELECT 
    RootPeopleId,
    SourcePeopleId,
    TargetPeopleId,
    TreeLevel
FROM RecursivePeople
ORDER BY RootPeopleId, TreeLevel;

代码说明 #

1. 初始成员：为每个根节点初始化VisitedNodes，用逗号包裹节点ID（避免部分匹配，比如ID=1和ID=11的混淆）。
2. 递归判断条件：使用CHARINDEX检查目标节点ID是否已存在于VisitedNodes中，仅当不存在时才继续递归。
3. 更新已访问集合：每次递归时将当前目标节点ID追加到VisitedNodes中，确保后续迭代不会重复处理该节点。

验证效果 #

• 针对添加的重复关系（ID=14：9→2）：递归过程中会检测到2已在根节点1的访问集合中，不会重复生成记录。
• 针对触发循环的关系（ID=15：9→3）：当递归到9时，检查3已在根节点1的访问集合中，终止该路径的递归，避免无限循环。

扩展优化 #

如果人员ID范围较大或数据量较多，使用字符串存储已访问集合可能存在性能瓶颈，可改用XML类型或JSON数组来存储已访问节点，配合exist()或JSON_QUERY进行更高效的存在性检查：

XML版本示例 #

WITH RecursivePeople(RootPeopleId, SourcePeopleId, TargetPeopleId, TreeLevel, VisitedNodes) AS
(
    SELECT 
        Id AS RootPeopleId,
        CAST(NULL AS INT) AS SourcePeopleId,
        Id AS TargetPeopleId,
        0 AS TreeLevel,
        CAST('<Nodes><Node>' + CAST(Id AS VARCHAR(10)) + '</Node></Nodes>' AS XML) AS VisitedNodes
    FROM People

    UNION ALL

    SELECT 
        rp.RootPeopleId,
        pr.SourcePeopleId,
        pr.TargetPeopleId,
        rp.TreeLevel + 1,
        rp.VisitedNodes.query('Nodes/Node')
        .query('concat("<Nodes>", ./Node, "<Node>", sql:column("pr.TargetPeopleId"), "</Node></Nodes>")')
    FROM PeopleRelationships pr
    JOIN RecursivePeople rp ON rp.TargetPeopleId = pr.SourcePeopleId
    WHERE rp.VisitedNodes.exist('/Nodes/Node[text()=sql:column("pr.TargetPeopleId")]') = 0
)
SELECT 
    RootPeopleId,
    SourcePeopleId,
    TargetPeopleId,
    TreeLevel
FROM RecursivePeople
ORDER BY RootPeopleId, TreeLevel;

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