生产订单回溯中的循环JOIN问题：寻求更优SQL解法

针对生产订单拆分后的关联回溯需求，完全可以用**递归CTE（公共表表达式）**替代原来的循环脚本，代码更简洁、性能更优，还能避免循环次数限制的问题。

核心递归查询代码 #

DECLARE @MyCode NVARCHAR(20) = '221209-6-R';

WITH RecursiveOrders AS (
    -- 锚点：初始目标订单
    SELECT 
        OrderID, 
        Code, 
        OrderTypeID
    FROM dbo.EventOrders
    WHERE Code = @MyCode

    UNION ALL

    -- 递归：逐层向上找前置订单
    SELECT 
        eo.OrderID, 
        eo.Code, 
        eo.OrderTypeID
    FROM dbo.EventSetOrderRelations esor
    JOIN dbo.EventOrders eo ON eo.OrderID = esor.OrderIDIn
    JOIN RecursiveOrders ro ON ro.OrderID = esor.OrderIDOut
)

SELECT * FROM RecursiveOrders ORDER BY OrderID ASC;

转成存储过程版本 #

CREATE PROCEDURE GetAllPredecessorOrders
    @TargetOrderCode NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    WITH RecursiveOrders AS (
        SELECT 
            OrderID, 
            Code, 
            OrderTypeID
        FROM dbo.EventOrders
        WHERE Code = @TargetOrderCode

        UNION ALL

        SELECT 
            eo.OrderID, 
            eo.Code, 
            eo.OrderTypeID
        FROM dbo.EventSetOrderRelations esor
        JOIN dbo.EventOrders eo ON eo.OrderID = esor.OrderIDIn
        JOIN RecursiveOrders ro ON ro.OrderID = esor.OrderIDOut
    )

    SELECT * FROM RecursiveOrders ORDER BY OrderID ASC;
END;

逻辑说明 #

1. 锚点成员：先定位到传入的目标订单（通过Code），作为递归的起点
2. 递归成员：通过EventSetOrderRelations关联表，把当前层级的订单（ro.OrderID作为子订单OrderIDOut）和它的前置订单（esor.OrderIDIn）关联，逐层向上回溯
3. 递归会自动终止，直到找不到更上层的前置订单为止，不需要手动判断终止条件

对比原循环脚本的优势 #

• 代码量减少一半以上，逻辑更直观
• 没有循环次数限制（原脚本限制100次，若层级超过100会截断）
• SQL Server会对递归CTE做优化，性能比手动循环更稳定

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