多批次库存分配难题。

[colin08]

大家好， 我想写一个库存分配的报表， 一点头绪都没有， 请大家帮帮忙。要求如下：

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 3

Table 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


不知大家有什么好的思路， 请大家指教！

[-晴天]

可以用递归算法实现.

[GoAwayZ]

用游标吧。

[colin08]

没用过游标，能不能详细一点？先在这谢谢你了

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

[叶子]

这两个表的主键是什么？

[叶子]

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

[cd731107]

--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