多批次库存分配难题。

Question

大家好， 我想写一个库存分配的报表， 一点头绪都没有， 请大家帮帮忙。要求如下：Table aa
item co order_qty     shipped_qty balance_qty
A         CO1 15 5 10
A         CO2 15 0 15
A         CO3 20 5 15
B         CO1 20 10 10
B         CO4 30 0 30
B         CO5 3 0 3Table bb
item lot inventory
A         A01      21
A         A02      3
B         B01      32
B         B03      12想得到如下表。
item co order_qty    shipped_qty balance_qty allocated          lot
A         CO1 15 5 10 10 A01
A         CO2 15 0 15 11 A01
A         CO2 15 0 15 3 A02
B         CO1 20 10 10 10 B01
B         CO4 30 0 30 22 B01
B         CO4 30 0 30 8 B03
B         CO5 3 0 3 3 B03
不知大家有什么好的思路， 请大家指教！

Answer 1

没用过游标，能不能详细一点？先在这谢谢你了item co order_qty shipped_qty balance_qty allocated lot
A          CO1 15 5 10 10 A01
A          CO2 15 0 15 11 A01
A          CO2 15 0 15 3 A02
B          CO1 20 10 10 10 B01
B          CO4 30 0 30 22 B01
B          CO4 30 0 30 8 B03
B          CO5 3 0 3 3 B03

Answer 2

参考一下：
http://blog.csdn.net/maco_wang/archive/2011/04/24/6347688.aspx

Answer 3

--1.对aa表建立临时表#a,zsa字段用于表示累计数
select *,zsa=(select sum(balance_qty) 
             from (select *,id=(select count(*) 
                                from aa as tb 
                                where tb.item=aa.item 
                                and tb.co<=aa.co)  
                   from aa) 
             as tb1 
where tb1.item=aa1.item and tb1.id<=aa1.id)
into #a
from 
( 
select *,id=(select count(*) from aa as tb where tb.item=aa.item and tb.co<=aa.co)  from aa
)  aa1--1.对bb表建立临时表#b,zsb字段用于表示累计数
select *,zsb=(select sum(inventory) 
             from (select *,id=(select count(*) 
                                from bb as tb 
                                where tb.item=bb.item 
                                and tb.lot<=bb.lot)  
                   from bb) 
             as tb1 
where tb1.item=bb1.item and tb1.id<=bb1.id)
into #b
from
(
select *,id=(select count(*) from bb as tb where tb.item=bb.item and tb.lot<=bb.lot)  from bb
) bb1
--3.将#a和#b根据对应关系组合成临时表#c,数量字段allocated暂定为0
select a.*,b.inventory,b.zsb,allocated=0,b.lot--case when a.zsa<=b.zsb then a.zsa else a.balance_qty-a.zsa+b.zsb end 
into #c
from #a a , #b b 
where a.item=b.item 
and
(   
(a.zsa<=b.zsb 
and   not exists (select 1 from #b c where c.item=b.item and a.zsa<c.zsb and c.zsb<b.zsb))
or 
(a.zsa>b.zsb 
and   not exists (select 1 from #a c where c.item=a.item and c.zsa>b.zsb and c.zsa<a.zsa))
)--4.利用变量更新数量字段allocated
declare @sl1 int,@sl2 int
declare @a varchar(1),@b varchar(1)update #c 
set @a=isnull(@b,'A'),
    @b=item,
    @sl2=case when @a=item then isnull(@sl1,zsa-zsb) else 0 end,
    @sl1=zsa-zsb,
    allocated=case when @sl2<=0 and zsa<=zsb then balance_qty
            when @sl2<=0 and zsa>zsb  then -@sl2
            when @sl2>0  and zsa>=zsb  then inventory
            when @sl2>0  and zsa<zsb   then @sl2            
            end 
--5.查询
select item,co,order_qty,shipped_qty,balance_qty,allocated,lot from #c/*
item co   order_qty   shipped_qty balance_qty allocated   lot  
---- ---- ----------- ----------- ----------- ----------- ---- 
A    CO1  15          5           10          10          A01
A    CO2  15          0           15          11          A01
A    CO2  15          0           15          3           A02
B    CO1  20          10          10          10          B01
B    CO4  30          0           30          22          B01
B    CO4  30          0           30          8           B03
B    CO5  3           0           3           3           B03（所影响的行数为 7 行）
*/--6.删除临时表
drop table #a,#b,#c