SQL实现FIFO规则扣减库存账龄报表库存差异咨询

核心扣减规则 #

扣减优先级从库龄最长的批次开始，逐段扣减至0后再扣下一段，顺序为：

• 第一优先级：Above60（60天以上库存）
• 第二优先级：days31to60（31-60天库存）
• 第三优先级：d30（30天内库存）
  所有账龄段扣减后的值不得小于0，其余非库存账龄字段保持原值不变。

实现代码 #

基于原有查询逻辑改造，使用CASE表达式逐段计算扣减后的值，兼容主流数据库：GREATEST函数在SQL Server 2022、MySQL 8.0及以上版本原生支持，低版本可将GREATEST(计算值, 0)替换为CASE WHEN 计算值 < 0 THEN 0 ELSE 计算值 END即可。

SELECT 
    itcode,
    Itcodeprd,
    -- 计算30天内库存扣减值
    CASE 
        WHEN diff <= Above60 THEN d30
        WHEN diff <= Above60 + days31to60 THEN d30
        ELSE GREATEST(d30 - (diff - Above60 - days31to60), 0)
    END AS d30,
    -- 计算31-60天库存扣减值
    CASE 
        WHEN diff <= Above60 THEN days31to60
        WHEN diff <= Above60 + days31to60 THEN GREATEST(days31to60 - (diff - Above60), 0)
        ELSE 0
    END AS days31to60,
    -- 计算60天以上库存扣减值
    CASE 
        WHEN diff <= Above60 THEN GREATEST(Above60 - diff, 0)
        ELSE 0
    END AS Above60,
    total,
    TotalStock,
    diff
FROM (
    SELECT 
        a.*,
        s.TotalStock,
        (a.total - s.TotalStock) AS diff 
    FROM mak_stock_Ageing a
    INNER JOIN [mak_stock_ageing _allstock] s ON s.itcode = a.Itcodeprd
    WHERE a.itcode = 15201
) base_calc

样例数据验证 #

针对给出的物料15201测试数据：d30=100、days31to60=53、Above60=170、diff=218，计算过程如下：

1. 待扣差异218大于Above60的170，Above60全额扣减为0，剩余待扣差异=218-170=48
2. 剩余待扣差异48小于days31to60的53，days31to60扣减后值=53-48=5，剩余待扣差异=0
3. d30无剩余待扣额，保持原值100
   最终返回结果和预期完全一致：d30=100、days31to60=5、Above60=0。

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