SQL Server 2018中基于FIFO逻辑实现销售与采购订单配对

针对需求中按先进先出逻辑配对销售/采购订单、保留未匹配记录的要求，以下是基于累计数量区间匹配的实现方案，适配销售/采购数量不等的场景。

测试表结构与数据 #

先模拟符合需求的表结构及测试数据（假设按产品维度进行匹配，实际场景可根据业务调整）：

销售订单表（table1） #

CREATE TABLE table1 (
    SO_RowId INT PRIMARY KEY,
    SO_CreationDate DATETIME,
    SO_Qty INT,
    SO_Product VARCHAR(50)
);

INSERT INTO table1 VALUES
(1, '2024-01-01 08:00', 5, 'ProductA'),
(2, '2024-01-01 09:00', 3, 'ProductA'),
(3, '2024-01-02 10:00', 4, 'ProductB');

采购订单表（table2） #

CREATE TABLE table2 (
    PO_RowId INT PRIMARY KEY,
    PO_CreationDate DATETIME,
    PO_Qty INT,
    PO_Product VARCHAR(50)
);

INSERT INTO table2 VALUES
(1, '2024-01-01 07:00', 4, 'ProductA'),
(2, '2024-01-01 08:30', 6, 'ProductA'),
(3, '2024-01-02 09:00', 2, 'ProductB'),
(4, '2024-01-03 11:00', 3, 'ProductB');

核心FIFO配对SQL #

通过CTE分步实现累计计算、区间匹配、未匹配记录处理：

WITH SO_Cumulative AS (
    -- 计算销售订单的累计数量区间
    SELECT 
        SO_RowId,
        SO_CreationDate,
        SO_Qty,
        SO_Product,
        -- 累计到上一个订单的总量
        SUM(SO_Qty) OVER (PARTITION BY SO_Product ORDER BY SO_CreationDate, SO_RowId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS SO_Cumulative_Prev,
        -- 累计到当前订单的总量
        SUM(SO_Qty) OVER (PARTITION BY SO_Product ORDER BY SO_CreationDate, SO_RowId) AS SO_Cumulative_Total
    FROM table1
),
PO_Cumulative AS (
    -- 计算采购订单的累计数量区间
    SELECT 
        PO_RowId,
        PO_CreationDate,
        PO_Qty,
        PO_Product,
        SUM(PO_Qty) OVER (PARTITION BY PO_Product ORDER BY PO_CreationDate, PO_RowId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PO_Cumulative_Prev,
        SUM(PO_Qty) OVER (PARTITION BY PO_Product ORDER BY PO_CreationDate, PO_RowId) AS PO_Cumulative_Total
    FROM table2
),
FIFO_Matches AS (
    -- 匹配销售与采购的数量区间，计算实际匹配量
    SELECT 
        s.SO_RowId,
        s.SO_CreationDate,
        s.SO_Qty AS SO_Total_Qty,
        p.PO_RowId,
        p.PO_CreationDate,
        p.PO_Qty AS PO_Total_Qty,
        s.SO_Product,
        -- 计算重叠区间的匹配数量
        CASE 
            WHEN s.SO_Cumulative_Prev IS NULL AND p.PO_Cumulative_Prev IS NULL THEN MIN(s.SO_Qty, p.PO_Qty)
            WHEN s.SO_Cumulative_Prev IS NULL THEN MIN(s.SO_Cumulative_Total, p.PO_Cumulative_Total) - ISNULL(p.PO_Cumulative_Prev, 0)
            WHEN p.PO_Cumulative_Prev IS NULL THEN MIN(s.SO_Cumulative_Total, p.PO_Cumulative_Total) - ISNULL(s.SO_Cumulative_Prev, 0)
            ELSE MAX(0, MIN(s.SO_Cumulative_Total, p.PO_Cumulative_Total) - MAX(s.SO_Cumulative_Prev, p.PO_Cumulative_Prev))
        END AS Matched_Qty
    FROM SO_Cumulative s
    JOIN PO_Cumulative p ON s.SO_Product = p.PO_Product
    -- 筛选有数量重叠的记录
    WHERE (s.SO_Cumulative_Total > ISNULL(p.PO_Cumulative_Prev, 0)) 
      AND (p.PO_Cumulative_Total > ISNULL(s.SO_Cumulative_Prev, 0))
),
SO_Unmatched AS (
    -- 计算未匹配的销售订单
    SELECT 
        s.SO_RowId,
        s.SO_CreationDate,
        s.SO_Qty,
        s.SO_Product,
        s.SO_Qty - ISNULL(SUM(m.Matched_Qty), 0) AS Unmatched_Qty
    FROM table1 s
    LEFT JOIN FIFO_Matches m ON s.SO_RowId = m.SO_RowId
    GROUP BY s.SO_RowId, s.SO_CreationDate, s.SO_Qty, s.SO_Product
    HAVING s.SO_Qty - ISNULL(SUM(m.Matched_Qty), 0) > 0
),
PO_Unmatched AS (
    -- 计算未匹配的采购订单
    SELECT 
        p.PO_RowId,
        p.PO_CreationDate,
        p.PO_Qty,
        p.PO_Product,
        p.PO_Qty - ISNULL(SUM(m.Matched_Qty), 0) AS Unmatched_Qty
    FROM table2 p
    LEFT JOIN FIFO_Matches m ON p.PO_RowId = m.PO_RowId
    GROUP BY p.PO_RowId, p.PO_CreationDate, p.PO_Qty, p.PO_Product
    HAVING p.PO_Qty - ISNULL(SUM(m.Matched_Qty), 0) > 0
)
-- 合并匹配与未匹配结果
SELECT 
    SO_RowId,
    SO_CreationDate,
    SO_Total_Qty AS SO_Qty,
    Matched_Qty AS SO_Matched_Qty,
    SO_Total_Qty - Matched_Qty AS SO_Remaining_Qty,
    PO_RowId,
    PO_CreationDate,
    PO_Total_Qty AS PO_Qty,
    Matched_Qty AS PO_Matched_Qty,
    PO_Total_Qty - Matched_Qty AS PO_Remaining_Qty,
    SO_Product AS Product,
    'Matched' AS Status
FROM FIFO_Matches
UNION ALL
SELECT 
    SO_RowId,
    SO_CreationDate,
    SO_Qty,
    0 AS SO_Matched_Qty,
    Unmatched_Qty AS SO_Remaining_Qty,
    NULL AS PO_RowId,
    NULL AS PO_CreationDate,
    NULL AS PO_Qty,
    0 AS PO_Matched_Qty,
    NULL AS PO_Remaining_Qty,
    SO_Product AS Product,
    'Unmatched (Sales)' AS Status
FROM SO_Unmatched
UNION ALL
SELECT 
    NULL AS SO_RowId,
    NULL AS SO_CreationDate,
    NULL AS SO_Qty,
    0 AS SO_Matched_Qty,
    NULL AS SO_Remaining_Qty,
    PO_RowId,
    PO_CreationDate,
    PO_Qty,
    0 AS PO_Matched_Qty,
    Unmatched_Qty AS PO_Remaining_Qty,
    PO_Product AS Product,
    'Unmatched (Purchase)' AS Status
FROM PO_Unmatched
ORDER BY Product, Status, COALESCE(SO_CreationDate, PO_CreationDate), COALESCE(SO_RowId, PO_RowId);

方案说明 #

1. 累计数量区间：通过窗口函数SUM() OVER()按产品、订单创建时间+RowId排序，计算每个订单的累计数量范围，为后续区间匹配做基础。
2. 区间匹配：通过JOIN筛选销售与采购订单数量区间重叠的记录，计算重叠部分的数量作为实际匹配量，严格遵循FIFO顺序。
3. 未匹配处理：通过左连接匹配结果，计算每个订单的已匹配总量，剩余数量大于0的即为未匹配订单，单独保留记录。
4. 场景适配：支持同一产品下销售/采购总量不等的情况，同时保留所有未匹配的订单信息，满足每日清空重插数据的运行需求。

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