SQL实现：按门店需求分配货箱商品并匹配对应货箱编号求解

现有表说明 #

• Crates表：存储每个货箱内的当前商品数量
• Stores_demand表：存储每个SKU商品在各门店的需求数量

表结构及测试数据 #

create table stores_demand
(sku    varchar(15),
store1  int,
store2  int,
store3  int,
store4  int,
store5  int,
store6  int,
store7  int,
store8  int,
store9  int,
store10 int,
demand int);

INSERT INTO stores_demand
values
('A100000219003',20,20, 20, 20, 0,20,15,20,0,0,135),
('A500000001928',20,20,20,20,16,0,0,0,0,0,96),
('A500000003308',20,20,20,20,20,20,20,20,20,20,200),
('A500000006249',10,10,10,10,10,12,8,10,8,0,88),
('A500000007035',10,10,10,10,10,10,8,7,0,0,75),
('A500000007704',10,10,10,10,10,12,8,10,8,7,95),
('A500000008759',10,10,10,10,10,12,8,10,8,5,93),
('A500000008849',20,20,20,20,10,0,0,0,0,0,90),
('A500000009363',20,20,20,20,20,20,0,15,2,0,137),
('A500000010308',10,10,10,10,10,10,8,10,8,7,93),
('A500000010309',10,10,10,10,10,10,8,10,8,7,93),
('A500000011062',20,20,20,20,11,0,0,0,0,0,91),
('A500000011362',10,10,10,10,10,12,8,10,8,7,95),
('A500000011993',10,10,10,10,10,12,8,10,8,0,88),
('A500000012153',10,10,10,10,10,12,8,10,8,0,88),
('A500000014325',10,10,10,10,10,12,8,10,8,0,88),
('A500000014701',20,20,20,20,0,20,0,0,10,0,110),
('A500000014702',20,3,0,0,0,0,0,0,0,0,23);

create table crates  
(sku    varchar(15),
crate_id varchar(30),   
total_quantity int);

INSERT INTO crates
values
('A100000219003','SH20000217-75',40),
('A100000219003','SH20000217-78',40),
('A100000219003','SH20000217-79',40),
('A100000219003','SH20000217-83',40),
('A100000219003','SH20000217-84',42),
('A100000219003','SH20000217-85',42),
('A100000219003','SH20000217-88',43),
('A100000219003','SH20000218-88',40),
('A100000219003','SH20000267-57',45),
('A500000001928','35WR21010117',32),
('A500000001928','SH20000141-16',64),
('A500000003308','SH20000234-159',30),
('A500000003308','SH20000234-160',30),
('A500000003308','SH20000234-161',30),
('A500000003308','SH20000234-162',30),
('A500000003308','SH20000234-163',30),
('A500000003308','SH20000234-164',30),
('A500000003308','SH20000234-165',30),
('A500000003308','SH20000234-166',30),
('A500000003308','SH20000234-167',30),
('A500000003308','SH20000234-168',30),
('A500000003308','SH20000234-169',30),
('A500000003308','SH20000234-170',30),
('A500000003308','SH20000234-172',29),
('A500000006249','102WR20019824',50),
('A500000006249','99WR20019824',50),
('A500000007035','72WR20019824',75),
('A500000007704','15WR21000780',130),
('A500000008759','107WR21007704',40),
('A500000008759','SH21000047-87',53),
('A500000008849','201WR21001888',20),
('A500000008849','202WR21001888',20),
('A500000008849','203WR21001888',20),
('A500000008849','204WR21001888',10),
('A500000008849','63WR21011733',20),
('A500000009363','SH20000141-14',80),
('A500000009363','SH20000141-15',37),
('A500000009363','SH20000141-16',20),
('A500000010308','64WR21010979',220),
('A500000010309','35WR21010979',220),
('A500000011062','50WR21008778',5),
('A500000011062','52WR21008778',25),
('A500000011062','6WR21014685',8),
('A500000011062','SH20000141-24',53),
('A500000011362','138WR21010218',100),
('A500000011993','19WR21014310',100),
('A500000012153','70WR20019824',250),
('A500000014325','30WR21000780',140),
('A500000014325','31WR21000780',110),
('A500000014701','126WR21008778',90),
('A500000014701','28WR21010117',24),
('A500000014702','31WR21010117',23);

需求说明 #

生成结果表，按照货箱容量、门店需求上限将商品分配到各门店，返回各门店分配量，以及需领用的货箱编号。分配规则：已拆箱剩余库存优先使用，现有库存满足所有门店需求时无需开启新货箱。结果表中get_crate字段返回需领用的货箱编号，无需领用则返回NULL。

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