基于多条件拆分数据行：递归CTE实现库存周消耗拆分需求

针对你需要按周销量拆分每个商品批次库存的需求，以下是基于递归CTE的实现代码：

declare @data table
(
item varchar(5)
,batch_stock int
,expiry_date datetime
,avg_weekly_sales int
)

insert into @data 
values 
    ('67007',   50, '2022-10-11 00:00:00.000',47),
    ('67007',   125,    '2022-11-16 00:00:00.000',47),
    ('67004',   71, '2022-10-11 00:00:00.000',51),
    ('67004',   183,'2022-11-07 00:00:00.000',51),
    ('67005',   138,    '2022-11-07 00:00:00.000',36),
    ('67005',   140,    '2022-10-24 00:00:00.000',36);

with StockSplit as (
    -- 锚点成员：初始化第一行数据，计算剩余库存和当前周次
    select 
        item,
        batch_stock as original_stock,
        expiry_date,
        avg_weekly_sales,
        case when batch_stock >= avg_weekly_sales then avg_weekly_sales else batch_stock end as weekly_usage,
        batch_stock - case when batch_stock >= avg_weekly_sales then avg_weekly_sales else batch_stock end as remaining_stock,
        1 as week_number
    from @data
    where batch_stock > 0 -- 排除无库存的批次
    union all
    -- 递归成员：继续拆分剩余库存，直到剩余库存为0
    select 
        ss.item,
        ss.original_stock,
        ss.expiry_date,
        ss.avg_weekly_sales,
        case when ss.remaining_stock >= ss.avg_weekly_sales then ss.avg_weekly_sales else ss.remaining_stock end as weekly_usage,
        ss.remaining_stock - case when ss.remaining_stock >= ss.avg_weekly_sales then ss.avg_weekly_sales else ss.remaining_stock end as remaining_stock,
        ss.week_number + 1 as week_number
    from StockSplit ss
    where ss.remaining_stock > 0
)
-- 最终输出结果
select 
    item,
    original_stock as batch_stock,
    expiry_date,
    avg_weekly_sales,
    weekly_usage,
    week_number
from StockSplit
order by item, expiry_date, week_number;

代码逻辑说明 #

1. 锚点成员：

   • 从原始数据集@data读取每个批次的基础信息
   • 计算第一周的使用量：库存≥周销量则取周销量，否则取全部剩余库存
   • 计算扣减后的剩余库存，标记周次为1

2. 递归成员：

   • 基于上一轮的剩余库存，重复计算当周使用量和新的剩余库存
   • 周次逐次加1
   • 终止条件：剩余库存为0时停止递归

3. 最终输出：

   • 保留原始批次库存、过期日期等信息
   • 展示每一周的使用量和对应周次
   • 按商品、过期日期、周次排序，确保结果有序

这个方案会自动处理所有批次场景，包括库存刚好整除周销量、库存小于周销量的情况。

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