SQL先进先出(FIFO)出库核销高效实现方案问询

目前存在物料清单（BOM）和领料单数据，需根据BOM的需求数量及先进先出（FIFO）原则，匹配BOM所领用的领料单。当前想到的方案是使用游标逐行判断并记录核销数量，但数据量较大时执行效率极低，且自行编写的SQL方案结果错误，是否存在更高效的正确解决方案？

IF OBJECT_ID('tempdb.dbo.#BOM') IS NOT NULL DROP TABLE #BOM
CREATE TABLE #BOM
(
    in_Code NVARCHAR(60), -- 入库编码
    in_Date DATETIME, -- 入库日期
    Orders NVARCHAR(60), -- 订单号
    BOM_materials NVARCHAR(60), -- BOM物料编码
    in_Qty DECIMAL(18,4) -- 入库数量
)
INSERT INTO #BOM
(in_Code, in_Date, Orders, BOM_materials, in_Qty)
VALUES (N'RK0001', '2022-03-08', N'A01', N'L01', 50),
       (N'RK0001', '2022-03-08', N'A01', N'L02', 30),
       (N'RK0002', '2022-03-15', N'A01', N'L01', 50)
SELECT * FROM #BOM

BOM数据示例：

IF OBJECT_ID('tempdb.dbo.#picking') IS NOT NULL DROP TABLE #picking
CREATE TABLE #picking
(
    out_Code NVARCHAR(60), -- 出库编码
    out_Date DATETIME, -- 出库日期
    Orders NVARCHAR(60), -- 订单号
    materials NVARCHAR(60), -- 物料编码
    out_Qty DECIMAL(18,4), -- 出库数量
    out_ID BIGINT -- 出库ID
)
INSERT INTO #picking
(out_Code, out_Date, Orders, materials, out_Qty, out_ID)
VALUES (N'CK0001', '2022-01-08', N'A01', N'L01', 90, 1),
       (N'CK0002', '2022-01-20', N'A01', N'L01', 70, 2),
       (N'CK0003', '2022-01-30', N'A01', N'L02', 10, 3)
SELECT * FROM #picking

领料单数据示例：

SELECT A.in_Code, A.in_Date, A.Orders, A.BOM_materials, A.in_Qty, B.out_Code, B.out_Qty, 
    CASE 
            WHEN (
                    SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                    FROM #picking 
                    WHERE Orders = A.Orders     
                        AND materials = A.BOM_materials
                        AND out_Date <= B.out_Date
                    ) 
                    >= 0 THEN B.out_Qty
            ELSE
                CASE 
                    WHEN (
                            SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                            FROM #picking 
                            WHERE Orders = A.Orders     
                                AND materials = A.BOM_materials
                                AND out_Date < B.out_Date
                            ) 
                            < 0 THEN 0
                        ELSE (
                                SELECT A.in_Qty - ( ISNULL(SUM(out_Qty),0) ) 
                                FROM #picking 
                                WHERE Orders = A.Orders     
                                    AND materials = A.BOM_materials
                                    AND out_Date < B.out_Date
                            ) 
                END
        END  AS need_Qty, B.out_ID
FROM #BOM A
    LEFT JOIN #picking B ON A.Orders = B.Orders AND A.BOM_materials = B.materials

无需使用游标，通过窗口函数计算累计数量即可实现FIFO匹配，性能远优于逐行处理。以下是SQL Server环境下的实现代码：

-- 计算领料单的累计出库区间
WITH PickingCumulative AS (
    SELECT 
        out_Code, out_Date, Orders, materials, out_Qty, out_ID,
        -- 订单+物料分组，按出库日期/ID排序的累计出库量
        SUM(out_Qty) OVER (PARTITION BY Orders, materials ORDER BY out_Date, out_ID) AS Cumulative_Out,
        -- 当前领料单的起始累计量（前序累计值）
        ISNULL(SUM(out_Qty) OVER (PARTITION BY Orders, materials ORDER BY out_Date, out_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS Prev_Cumulative_Out
    FROM #picking
),
-- 计算BOM的累计需求区间
BOMCumulative AS (
    SELECT 
        in_Code, in_Date, Orders, BOM_materials, in_Qty,
        -- 订单+物料分组，按入库日期/编码排序的累计需求量
        SUM(in_Qty) OVER (PARTITION BY Orders, BOM_materials ORDER BY in_Date, in_Code) AS Cumulative_In,
        -- 当前BOM的起始累计量（前序累计值）
        ISNULL(SUM(in_Qty) OVER (PARTITION BY Orders, BOM_materials ORDER BY in_Date, in_Code ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS Prev_Cumulative_In
    FROM #BOM
)
-- 匹配区间并计算核销数量
SELECT 
    B.in_Code, B.in_Date, B.Orders, B.BOM_materials, B.in_Qty,
    P.out_Code, P.out_Qty,
    -- 取两个区间的交集长度作为核销数量
    CASE
        WHEN P.Cumulative_Out <= B.Prev_Cumulative_In THEN 0
        WHEN P.Prev_Cumulative_Out >= B.Cumulative_In THEN 0
        ELSE LEAST(B.Cumulative_In, P.Cumulative_Out) - GREATEST(B.Prev_Cumulative_In, P.Prev_Cumulative_Out)
    END AS need_Qty,
    P.out_ID
FROM BOMCumulative B
JOIN PickingCumulative P 
    ON B.Orders = P.Orders 
    AND B.BOM_materials = P.materials
WHERE
    -- 过滤无交集的记录
    NOT (P.Cumulative_Out <= B.Prev_Cumulative_In OR P.Prev_Cumulative_Out >= B.Cumulative_In)
ORDER BY B.in_Code, P.out_Date

方案说明 #

1. PickingCumulative：按订单+物料分组，计算每张领料单的累计出库量区间，明确当前领料单的出库量在整体序列中的位置。
2. BOMCumulative：按订单+物料分组，计算每个BOM记录的累计需求量区间，明确当前BOM的需求在整体序列中的位置。
3. 区间匹配：通过判断需求区间和出库区间的交集，计算交集部分的数量即为核销数量，自动过滤无关联的记录。

该方案基于窗口函数实现，避免了游标和嵌套查询的性能瓶颈，大数据量场景下仍能高效执行。

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