在SQL中实现FIFO库存计价：基于库存与销售表的计算需求

针对你的库存表和销售表，要实现FIFO的成本计算及剩余库存统计，可以用窗口函数结合条件判断来完成，以下是具体的SQL实现步骤：

1. 计算库存批次的累计可用量 #

先对同一商品的库存按采购日期排序，计算每个批次的累计库存，明确销售时优先消耗的批次顺序：

WITH stock_with_running_total AS (
    SELECT 
        ID,
        Qty AS original_qty,
        DatePurchased,
        Price,
        SUM(Qty) OVER (PARTITION BY ID ORDER BY DatePurchased) AS running_total
    FROM stock_table
)

2. 关联销售表，计算各批次的耗用数量、剩余库存及批次成本 #

将临时表与销售表关联，通过累计库存和销售数量的对比，判断每个批次被消耗的数量，进而算出剩余库存和该批次的成本：

, fifo_calculation AS (
    SELECT 
        s.ID,
        s.DatePurchased,
        s.original_qty,
        s.Price,
        -- 计算该批次被耗用的数量
        CASE
            WHEN s.running_total <= i.Qty THEN s.original_qty
            WHEN s.running_total - s.original_qty >= i.Qty THEN 0
            ELSE i.Qty - (s.running_total - s.original_qty)
        END AS used_qty,
        -- 剩余库存 = 原库存 - 耗用数量
        s.original_qty - CASE
            WHEN s.running_total <= i.Qty THEN s.original_qty
            WHEN s.running_total - s.original_qty >= i.Qty THEN 0
            ELSE i.Qty - (s.running_total - s.original_qty)
        END AS remaining_qty,
        -- 该批次的成本 = 耗用数量 * 单价
        CASE
            WHEN s.running_total <= i.Qty THEN s.original_qty * s.Price
            WHEN s.running_total - s.original_qty >= i.Qty THEN 0
            ELSE (i.Qty - (s.running_total - s.original_qty)) * s.Price
        END AS batch_cost
    FROM stock_with_running_total s
    JOIN item_sold_table i ON s.ID = i.ID
    WHERE s.ID = 11202
)

3. 查看明细及汇总总成本 #

执行以下语句可查看各批次的耗用、剩余情况，以及销售的总成本：

-- 查看各批次明细
SELECT * FROM fifo_calculation;

-- 汇总销售总成本
SELECT ID, SUM(batch_cost) AS total_cost FROM fifo_calculation GROUP BY ID;

用你的数据测试会得到预期结果：

• 2023-01-01批次：used_qty=4，remaining_qty=0，batch_cost=12
• 2023-06-12批次：used_qty=2，remaining_qty=1，batch_cost=14
• 2023-12-10批次：used_qty=0，remaining_qty=4，batch_cost=0
  汇总总成本为26，完全符合FIFO计价逻辑。

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