递归CTE SQL返回行数多于源数据集的原因及排查

问题根因 #

查询返回行数远超源数据10行，核心由三类问题导致：

• JOIN条件存在书写错误：关联条件ts.Prev_Article, = cte.Current_Article,中字段后多余的逗号会触发数据库非预期解析，产生隐性笛卡尔积匹配
• 递归关联缺少层级递进约束：现有逻辑仅通过Prev_Article和Top_lvl_article关联，没有限制子节点层级必须为父节点层级+1，会出现跨层重复匹配、甚至循环关联问题，最终生成大量重复/无效路径行
• 字段逻辑设计缺陷：递归过程中直接覆盖原始Qty字段做累计，没有保留节点原始数量值，最终输出无法同时展示单节点原始用量和路径累计用量

修复后可直接运行的代码 #

WITH CTE_Top_levels AS (
    -- 锚点成员：取所有层级为0的顶层节点，初始累计数量等于节点自身数量
    SELECT 
        ts.Lvl,
        ts.[Order],
        ts.Top_lvl_article,
        ts.Prev_Article,
        ts.Current_Article,
        ts.Qty,
        ts.Qty AS Total_qty
    FROM #t_sample_dataset ts
    WHERE ts.Lvl = 0

    UNION ALL

    -- 递归成员：每次仅遍历直接下一层子节点，累计值=父级累计值+当前节点自身数量
    SELECT 
        ts.Lvl,
        ts.[Order],
        ts.Top_lvl_article,
        ts.Prev_Article,
        ts.Current_Article,
        ts.Qty,
        cte.Total_qty + ts.Qty AS Total_qty
    FROM CTE_Top_levels cte
    INNER JOIN #t_sample_dataset ts
        ON cte.Top_lvl_article = ts.Top_lvl_article
        AND cte.Current_Article = ts.Prev_Article -- 修正原语句多余逗号的书写错误
        AND ts.Lvl = cte.Lvl + 1 -- 核心约束：仅关联直接下一级节点，彻底避免跨层匹配、循环关联
)
-- 最终返回结果与源表行数一致，共10行，包含所有原始字段+路径累计值
SELECT Lvl, [Order], Top_lvl_article, Prev_Article, Current_Article, Qty, Total_qty
FROM CTE_Top_levels
ORDER BY [Order];

关键注意事项 #

• ts.Lvl = cte.Lvl + 1是解决行数膨胀的核心条件，加完该约束后递归每次只会向下遍历一级子节点，不会生成冗余路径
• Order是多数SQL方言的保留关键字，编写查询时需要用方括号（SQL Server）或反引号（MySQL/PostgreSQL）包裹，避免语法报错
• 如果物料BOM存在异常循环引用（比如某子节点的上级指向自身后代），可以在递归中新增路径存储字段，提前判断节点是否已在当前遍历路径中，避免无限递归。

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