表A:商品条形码,进货日期,进货数量
表B:商品条形码,销售数量
怎么实现把表B中的记录去表A中从进货日期最早的开始减,也就是查询的时候要实现商品的先进先出算法
用游标实现如下:
declare @Number as int
declare @Numbered as int
declare @id as nvarchar(50)
declare @BarCode varchar(13)
declare @StockNum int
set @Number=0
set @Numbered=0
declare tb cursor local
for select 商品条形码,sum(销售数量) from t_商品出货表新 b group by 商品条形码
open tb
fetch tb into @barcode,@StockNum
while @@fetch_status=0
begin
while @StockNum>0
begin
select top 1 @id=id,@Number=进货数量,@Numbered=已出货数量 from T_商品进货表新 where 商品条形码=@BarCode and 进货数量<>已出货数量 order by 进货日期
if @StockNum>@Number-@Numbered
Update T_商品进货表新 set 已出货数量=已出货数量+@Number-@Numbered where ID=@id
else
Update T_商品进货表新 set 已出货数量=已出货数量+@StockNum where ID=@id
set @StockNum=@StockNum-(@Number-@Numbered)
end
fetch tb into @barcode,@StockNum
end
close tb
deallocate tb
select * from t_商品进货新表
用游标实现实在太慢了,有没有更有效率的方法,谢谢!
表B:商品条形码,销售数量
怎么实现把表B中的记录去表A中从进货日期最早的开始减,也就是查询的时候要实现商品的先进先出算法
用游标实现如下:
declare @Number as int
declare @Numbered as int
declare @id as nvarchar(50)
declare @BarCode varchar(13)
declare @StockNum int
set @Number=0
set @Numbered=0
declare tb cursor local
for select 商品条形码,sum(销售数量) from t_商品出货表新 b group by 商品条形码
open tb
fetch tb into @barcode,@StockNum
while @@fetch_status=0
begin
while @StockNum>0
begin
select top 1 @id=id,@Number=进货数量,@Numbered=已出货数量 from T_商品进货表新 where 商品条形码=@BarCode and 进货数量<>已出货数量 order by 进货日期
if @StockNum>@Number-@Numbered
Update T_商品进货表新 set 已出货数量=已出货数量+@Number-@Numbered where ID=@id
else
Update T_商品进货表新 set 已出货数量=已出货数量+@StockNum where ID=@id
set @StockNum=@StockNum-(@Number-@Numbered)
end
fetch tb into @barcode,@StockNum
end
close tb
deallocate tb
select * from t_商品进货新表
用游标实现实在太慢了,有没有更有效率的方法,谢谢!
set 已出货数量=已出货数量+ case when c.销售数量> 进货数量 - 已出货数量 then 进货数量 - 已出货数量
else c.销售数量 end
from T_商品进货表新 d
right join
( select id,进货数量,已出货数量,c.销售数量
from T_商品进货表新 a
right join
(select 商品条形码,sum(销售数量) as 销售数量 from t_商品出货表新 b group by 商品条形码 )c
on a.商品条形码 = c.商品条形码
where not exists(select 1 from T_商品进货表新 where 商品条形码 = a.商品条形码 and 进货日期 > a.进货日期)
and 进货数量 <>已出货数量) b
on d.id = b.id
http://topic.csdn.net/u/20080614/11/72b44706-5c54-4b38-b6d4-ef31c17da0c7.html