如何用SQL按先进先出规则匹配采购销售记录并计算利润差

核心逻辑是通过窗口函数计算采购、销售的累计数量区间，匹配区间重叠部分即可得到对应采购单和销售单的匹配数量，无需游标或循环即可实现批量计算。

生成利润表tblProfitLoss的SQL语句 #

WITH 
-- 计算每个采购单的库存覆盖区间
purchase_cumu AS (
    SELECT 
        pID,
        Product,
        pTransRate,
        pQty,
        SUM(pQty) OVER (PARTITION BY Product ORDER BY transDate, pID) - pQty AS lower_bound,
        SUM(pQty) OVER (PARTITION BY Product ORDER BY transDate, pID) AS upper_bound
    FROM tblPurchaseRecords
),
-- 计算每个销售单的销量覆盖区间
sale_cumu AS (
    SELECT 
        sID,
        Product,
        sTransRate,
        sQty,
        SUM(sQty) OVER (PARTITION BY Product ORDER BY transDate, sID) - sQty AS lower_bound,
        SUM(sQty) OVER (PARTITION BY Product ORDER BY transDate, sID) AS upper_bound
    FROM tblSaleRecords
)
-- 匹配区间重叠部分得到利润明细
INSERT INTO tblProfitLoss (pID, sID, plQty, plRate, plValue)
SELECT 
    p.pID,
    s.sID,
    LEAST(p.upper_bound, s.upper_bound) - GREATEST(p.lower_bound, s.lower_bound) AS plQty,
    (REPLACE(s.sTransRate, '$', '') + 0) - (REPLACE(p.pTransRate, '$', '') + 0) AS plRate,
    (LEAST(p.upper_bound, s.upper_bound) - GREATEST(p.lower_bound, s.lower_bound)) 
    * ((REPLACE(s.sTransRate, '$', '') + 0) - (REPLACE(p.pTransRate, '$', '') + 0)) AS plValue
FROM purchase_cumu p
INNER JOIN sale_cumu s 
    ON p.Product = s.Product
    AND p.lower_bound < s.upper_bound
    AND s.lower_bound < p.upper_bound
ORDER BY p.pID, s.sID;

更新采购、销售表剩余数量 #

-- 更新采购单剩余库存
UPDATE tblPurchaseRecords pr
INNER JOIN (
    SELECT pID, SUM(plQty) AS used_qty FROM tblProfitLoss GROUP BY pID
) pl ON pr.pID = pl.pID
SET pr.pRemaining = pr.pQty - pl.used_qty;

-- 更新销售单剩余待匹配数量
UPDATE tblSaleRecords sr
INNER JOIN (
    SELECT sID, SUM(plQty) AS sold_qty FROM tblProfitLoss GROUP BY sID
) pl ON sr.sID = pl.sID
SET sr.sRemaining = sr.sQty - pl.sold_qty;

注意事项 #

• 本方案适配所有支持SQL窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等），低版本不支持窗口函数的数据库可将累计值计算改为子查询实现
• 若你的transRate字段本身为数值格式，无$符号，直接删除代码中的REPLACE转换逻辑即可
• 代码默认按商品维度隔离计算，多商品场景无需额外修改逻辑
• 计算结果与你给出的示例完全一致，可直接执行验证

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