引用单表的递归SQL查询陷入无限循环，寻求解决方案

无限循环原因 #

1. 连接逻辑反向：原递归部分的连接条件variable.num = b.number逻辑错误，应该用递归结果中的父节点编号关联原表中子节点的父编号字段，而非直接让父节点编号与子节点编号相等，这会导致节点被反复匹配，甚至触发循环引用。
2. 递归条件冗余：递归部分仍保留b.condition = 99999，会让递归每次都去匹配根节点级别的数据，而非向下查找子节点，导致无限重复查询根节点相关内容。
3. 无循环终止约束：未限制递归过程中不重复处理已访问节点，若数据存在循环引用（比如A的父是B，B的父是A），会直接陷入死循环。

修正方案 #

假设你的table表存在parent_number字段（存储当前节点的父节点编号），修正后的SQL如下：

WITH XYZ AS (
    -- 锚点查询：获取根节点（level=1）
    SELECT a, number, c, d, 1 AS level, CAST(number AS VARCHAR(MAX)) AS path
    FROM table
    WHERE condition = 99999

    UNION ALL

    -- 递归查询：获取子节点，level递增
    SELECT b.a, b.number, b.c, b.d, variable.level + 1 AS level, 
           variable.path + '->' + CAST(b.number AS VARCHAR(MAX)) AS path
    FROM table b
    JOIN XYZ variable ON variable.number = b.parent_number  -- 正确关联父节点与子节点
    -- 排除已访问路径，防止循环引用
    WHERE CHARINDEX(CAST(b.number AS VARCHAR(MAX)), variable.path) = 0
)
SELECT a, number, c, d, level
FROM XYZ
ORDER BY level, number;

关键改动说明 #

• 修正连接逻辑：将variable.num = b.number改为variable.number = b.parent_number，确保递归时从父节点向下查找对应子节点。
• 移除冗余条件：递归部分删除b.condition = 99999，仅在锚点查询中筛选根节点。
• 添加路径追踪：新增path字段记录节点访问路径，通过CHARINDEX检查当前节点是否已在路径中，彻底避免循环引用导致的无限递归。
• 优化排序：ORDER BY中增加number，让同层级节点按编号有序排列。

预期输出 #

修正后的查询会生成你期望的层级化结果：

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