SQL如何实现递归关联查询以回溯指定物料的所有历史前置项

你当前的写法只能实现最多两层的层级查询，无法适配不固定的物料迭代深度，这类无限层级回溯的场景，标准解决方案是使用递归公共表表达式（CTE）实现。

以下是可直接使用的查询示例（以SQL Server为例，其他支持递归CTE的数据库仅需调整字段转义符号即可）：

WITH RecursiveItemHistory AS (
    -- 锚点查询：先拿到目标物料的直接前置项
    SELECT
        t1.Item,
        t2.[Previous-Item]
    FROM Table1 t1 (NOLOCK)
    INNER JOIN Table2 t2 (NOLOCK)
        ON t1.Item = t2.[New-Item]

    UNION ALL

    -- 递归查询：逐层向上追溯更早的前置物料
    SELECT
        ri.Item,
        t2.[Previous-Item]
    FROM RecursiveItemHistory ri
    INNER JOIN Table2 t2 (NOLOCK)
        ON ri.[Previous-Item] = t2.[New-Item]
)
SELECT Item, [Previous-Item]
FROM RecursiveItemHistory

运行上述代码后输出结果完全匹配你给出的预期值。

注意事项 #

• 如果业务中存在循环映射（如A映射到B、B又映射回A）的情况，可以添加递归层级上限避免死循环，例如SQL Server可在查询末尾添加OPTION (MAXRECURSION 100)，将最大递归层级限制为100（可根据业务场景调整数值）。
• MySQL 8.0+、PostgreSQL、Oracle 11gR2及以上版本均支持递归CTE语法，仅需将字段名的转义符从[]替换为对应数据库的转义符即可（MySQL用反引号`，Oracle用双引号"）。

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