父产品与子产品代码相同时，递归查询无法返回记录问题

我之前处理过类似的递归CTE问题，你碰到的情况核心是**自引用记录（父产品=子产品）**没有被正确纳入递归逻辑，同时还要避免无限循环的问题。下面结合你的测试代码给出具体解决方案：

第一步：补全测试数据（模拟你的场景） #

先把测试表填充完整，包含一条自引用的核心记录：

USE DB_TEST
DECLARE @TableTest TABLE (
 Product nvarchar(10) null,
 ParentProduct nvarchar(10) null,
 Machine nvarchar(10) null
)
BEGIN
    -- 插入测试数据：包含正常层级+自引用记录
    INSERT INTO @TableTest VALUES ('ProA', 'ProB', 'Machine1');
    INSERT INTO @TableTest VALUES ('ProB', 'ProB', 'Machine2'); -- 自引用的关键记录
    INSERT INTO @TableTest VALUES ('ProC', 'ProA', 'Machine3');

第二步：修正递归CTE逻辑 #

下面的代码同时解决了包含自引用记录和防止无限递归两个核心问题：

-- 修正后的递归CTE
    WITH ProductHierarchy AS (
        -- 锚点查询：直接包含自引用记录，同时保留正常根节点
        SELECT 
            Product, 
            ParentProduct, 
            Machine,
            1 AS RecursionLevel -- 添加层级字段，控制递归深度
        FROM @TableTest
        -- 若需查询特定产品层级，可改为 WHERE Product = '你的目标产品'
        WHERE ParentProduct IS NULL OR Product = ParentProduct

        UNION ALL

        -- 递归查询：只处理非自引用的子节点，避免无限循环
        SELECT 
            t.Product, 
            t.ParentProduct, 
            t.Machine,
            ph.RecursionLevel + 1 AS RecursionLevel
        FROM @TableTest t
        INNER JOIN ProductHierarchy ph ON t.ParentProduct = ph.Product
        WHERE t.Product != t.ParentProduct -- 跳过自引用，防止循环
          AND ph.RecursionLevel < 100 -- 最大层级限制，避免意外无限递归
    )
    SELECT Product, ParentProduct, Machine FROM ProductHierarchy;
END

关键调整说明 #

• 锚点查询：不再排除Product = ParentProduct的记录，确保自引用产品能被初始加载
• 递归层级控制：添加RecursionLevel字段，既防止自引用导致的无限循环错误，也能清晰看到递归深度
• 递归过滤：在递归步骤里跳过自引用记录，这类记录已在锚点中包含，无需重复递归

如果你的需求是从子产品向上追溯父产品，只需调整锚点的WHERE条件即可：

-- 向上追溯父产品的示例
WITH ProductHierarchy AS (
    SELECT 
        Product, 
        ParentProduct, 
        Machine,
        1 AS RecursionLevel
    FROM @TableTest
    WHERE Product = 'ProC' -- 目标子产品

    UNION ALL

    SELECT 
        t.Product, 
        t.ParentProduct, 
        t.Machine,
        ph.RecursionLevel + 1
    FROM @TableTest t
    INNER JOIN ProductHierarchy ph ON t.Product = ph.ParentProduct
    WHERE t.Product != t.ParentProduct -- 避免自引用循环
      AND ph.RecursionLevel < 100
)
SELECT * FROM ProductHierarchy;

总结排查要点 #

1. 检查原CTE的锚点查询，是否误加了ParentProduct != Product这类过滤条件
2. 必须添加递归层级限制，否则自引用会触发SQL Server的「递归超过最大次数」错误
3. 根据业务需求（向下展开/向上追溯）调整锚点的初始查询条件

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