不能直接用SUM() OVER做FIFO库存扣减，因为其仅做静态累加，无法模拟“先入库、先扣完”的逐笔状态迁移过程；FIFO要求显式追踪剩余可扣库存和每笔实际消耗量，必须借助递归CTE等能维护行间依赖的机制实现。
为什么不能直接用 SUM() OVER 做 FIFO 库存扣减
因为 SUM() OVER 是累加，不是“逐笔消耗”。库存扣减本质是状态迁移：每条出库记录要按入库时间顺序，从最早那批库存里扣，扣完才轮到下一批。窗口函数本身不维护行间依赖关系，单纯累加会把所有入库量堆在一起，无法体现“先入库、先扣完”的业务逻辑。
常见错误写法：SUM(in_qty) OVER (ORDER BY in_time) - SUM(out_qty) OVER (ORDER BY out_time) —— 这种混用两个时间轴的累加，结果完全不可控，尤其当出入库时间交叉时，数值毫无业务意义。
用 LAG() + 递归 CTE 模拟逐行状态流转
FIFO 扣减必须显式追踪“当前剩余可扣库存”和“本笔实际扣减量”，这需要上一行的计算结果作为本行输入。标准 SQL 中，只有递归 CTE 或某些数据库的 MODEL 子句能表达这种依赖。PostgreSQL/SQL Server/Oracle 都支持递归 CTE；MySQL 8.0+ 也支持。
- 先按
in_time排序入库记录，生成带累计入库量的中间表 - 再按
out_time排序出库记录，与入库流做“指针式”匹配 - 递归中维护两个状态变量：
remaining_stock（当前还剩多少可扣）、consumed_from_id（正在消耗哪笔入库） - 每次递归取一条出库记录，从
remaining_stock扣减，不足则推进到下一笔入库
示例关键片段（PostgreSQL）：
WITH RECURSIVE fifo AS (
-- 初始：第一笔出库，从最早入库开始扣
SELECT
o.id AS out_id,
i.id AS in_id,
LEAST(o.qty, i.qty) AS taken,
i.qty - LEAST(o.qty, i.qty) AS remaining_in,
o.qty - LEAST(o.qty, i.qty) AS remaining_out
FROM out_records o
CROSS JOIN LATERAL (
SELECT * FROM in_records ORDER BY in_time LIMIT 1
) i
WHERE o.id = (SELECT MIN(id) FROM out_records)
UNION ALL
-- 递归：根据上一轮 remaining_out 和 remaining_in 决定下一步取哪笔
SELECT ...
)
ROW_NUMBER() 分组 + 自连接暴力匹配的适用场景
如果出库单量小（in_time 排序后取前 N 行，N 为还需扣减的数量。
- 需提前给入库记录加
ROW_NUMBER() OVER (ORDER BY in_time)作为唯一序号 - 自连接条件必须包含：
i1.in_time 且 <code>i1.rn —— 否则会漏掉跨时间的合法匹配 - 性能敏感场景慎用：1000 条出库 × 1000 条入库 = 百万级连接，PG 可能卡住，MySQL 可能超内存
- 适合一次性核对、报表导出等低频操作，不适合 OLTP 实时扣减
真实生产环境建议绕开纯 SQL 实现
真正稳定的 FIFO 库存扣减，几乎都放在应用层或存储过程里做。原因很实在：
- SQL 里维护多行状态容易出竞态：并发出库请求可能读到相同“剩余库存”，导致超扣
- 需要加锁范围大（比如锁住整条入库链），拖慢吞吐
- 退货、冲红、调拨等异常流程会让 SQL 逻辑爆炸式膨胀
- 哪怕用了递归 CTE，不同数据库对递归深度限制不同（PostgreSQL 默认 100 层，SQL Server 可设但影响执行计划）
更可行的做法：入库时生成带唯一序号的库存批次（batch_id），出库时用应用代码按 batch_id 升序逐个扣减，扣完标记该批次为 exhausted。SQL 只负责原子更新和状态查询，状态流转逻辑交给应用控制。
