这是一个货物装箱的问题，表2是需要装箱的数据，表1是装箱规则。
根据表1的实际包装件数，生成结果表，最重要的是处理结果表的箱号。
表数据如下，求助。
表1
包装件数 箱类型 最大件数 实际包装件数
41 A 10 10
41 B 20 19
41 C 13 12
表2
DC Stroe PO SKU Qty
sh s1 po1 SKU1 20
sh s1 po1 SKU2 21
结果
箱号 DC stroe PO SKU Qty 箱类型
1 sh s1 po1 SKU1 10 A
2 sh s1 po1 SKU1 10 B
2 sh s1 po1 SKU2 9 B
3 sh s1 po1 SKU2 12 C
------解决方案--------------------
2 sh s1 po1 SKU2 9 B -->A
3 sh s1 po1 SKU2 12 C
--這條數據是否正確，應該是A吧
------解决方案--------------------
貌似表1 的第二条数据有误,B的最大件数应该是10,表2中并没有一箱中装多少的设定,所以表1的中间一列还是有意义的.
------解决方案--------------------
给个递归进行工作量分配的程序你参考:
- SQL code
create table tb(Machine varchar(5),PNO varchar(10),PQty decimal(8,2),DayQty decimal(8,2),OrderQty int)insert into tb select 'A','001',100,40,10insert into tb select 'A','002',30,20,25insert into tb select 'A','003',20,10,36insert into tb select 'B','001',100,30,1insert into tb select 'B','002',40,10,4 go;with cte as(--不同机器的第一行select Machine,[Day]=1,PNO,AQty=convert(decimal(8,2),(case when PQty>dayQty then DayQty else PQty end)), PQty=convert(decimal(8,2),PQty-(case when PQty>DayQty then DayQty else PQty end)),OrderQty, Rate=convert(decimal(8,6),(case when PQty<DayQty then 1.0-PQty/DayQty else 0.0 end))from tb awhere OrderQty=(select top 1 OrderQty from tb where machine=a.machine order by OrderQty)union all--当某机器某OrderQty上一次序未生产完时select a.Machine,[Day]=a.[Day]+1,a.PNO,AQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then b.DayQty else a.PQty end)), PQty=convert(decimal(8,2),(case when a.PQty>b.DayQty then a.PQty-b.DayQty else 0.0 end)),a.OrderQty, Rate=convert(decimal(8,6),(case when a.PQty<b.DayQty then 1.0-a.PQty/b.DayQty else 0.0 end))from cte a inner join tb b on a.Machine=b.Machine and a.PNO=b.PNO where a.PQty>0union all--当某机器某QrderQty在某天生产完但剩余生产力时select a.Machine,a.[Day],b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty*a.rate then b.DayQty*a.Rate else b.PQty end)), PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty*a.Rate then b.DayQty*a.Rate else b.PQty end)),b.OrderQty, Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty*a.rate then (b.DayQty*a.Rate-b.PQty)/b.DayQty else 0 end))from cte a inner join tb b on a.Machine=b.Machinewhere a.Rate>0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)union all--当某天生产完某项OrderQty且生产力刚好用完时select a.Machine,[Day]=a.[Day]+1,b.PNO,AQty=convert(decimal(8,2),(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)), PQty=convert(decimal(8,2),b.PQty-(case when b.PQty>b.DayQty then b.DayQty else b.PQty end)),b.OrderQty, Rate=convert(decimal(8,6),(case when b.PQty<b.DayQty then 1.0-b.PQty*1.0/b.DayQty else 0 end))from cte a inner join tb b on a.Machine=b.Machinewhere a.Rate=0 and a.PQty=0 and a.AQty>0 and a.OrderQty<b.OrderQty and not exists(select 1 from tb where Machine=a.Machine and OrderQty>a.OrderQty and OrderQty<b.OrderQty)union all--添加各天不生产的部分select a.Machine,[Day]=a.[day],b.PNO,AQty=0,PQty=0,OrderQty=b.OrderQty,Raty=0.00from cte a inner join tb b on a.OrderQty<b.OrderQty and a.Machine=b.Machinewhere a.PQty>0 )select Machine,[Day],PNO,AQty,PQty from cte order by Machine,[Day],PNOgodrop table tb/*Machine Day PNO AQty PQty------- ----------- ---------- --------------------------------------- ---------------------------------------A 1 001 40.00 60.00A 1 002 0.00 0.00A 1 003 0.00 0.00A 2 001 40.00 20.00A 2 002 0.00 0.00A 2 003 0.00 0.00A 3 001 20.00 0.00A 3 002 10.00 20.00A 3 003 0.00 0.00A 4 002 20.00 0.00A 5 003 10.00 10.00A 6 003 10.00 0.00B 1 001 30.00 70.00B 1 002 0.00 0.00B 2 001 30.00 40.00B 2 002 0.00 0.00B 3 001 30.00 10.00B 3 002 0.00 0.00B 4 001 10.00 0.00B 4 002 6.67 33.33B 5 002 10.00 23.33B 6 002 10.00 13.33B 7 002 10.00 3.33B 8 002 3.33 0.00*/