有四个表:库存表Inventory,入库表BillIn,出库表BillOut,月结表AccountHId(货位),Mat_no(物品代码),Qty(数量),Price(单价),Amount(金额)月结表(Qty1上月结存数量,Qty2本月入库数量,Qty2本月出库数量,Qty3本月结存数量……)在月结账的时候我是这样做:declare @cmonth varchar(10)
declare @cmonth1 varchar(10)
set @cmonth ='2011-10' --传结账月份 参数
set @cmonth1 ='2011-09' --传上个月 参数--插入月结表
insert into Account (Cmonth,HId,Mat_no)
select @cmonth, HId,Mat_no from Inventory--更新本期期初
update Account set Qty1=(select Qty4 from Account a where Cmonth=@cmonth1 and Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount1=(select Amount4 from Account a where Cmonth=@cmonth1 and Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth--更新入库
update Account set Price2=(select sum(Amount)/sum(Qty) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Qty2=(select sum(Qty) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount2=(select sum(Amount) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth--更新出库
update Account set Price3= (Amount1+Amount2)/(Qty1+Qty2) where Cmonth=@cmonth
update Account set Qty3=(select sum(Qty) from BillOut a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount3=Price3 * Qty3 --更新本期结存
update Account set Qty4=Qty1+Qty2-Qty3 where Cmonth=@cmonth
update Account set Amount4=Amount1+Amount2-Amount3 where Cmonth=@cmonth
update Account set Price4=Amount4/Qty4
以上是我一个解决方法,问题是运行效率太差,有没有更好的解决方案呢?求教!
declare @cmonth1 varchar(10)
set @cmonth ='2011-10' --传结账月份 参数
set @cmonth1 ='2011-09' --传上个月 参数--插入月结表
insert into Account (Cmonth,HId,Mat_no)
select @cmonth, HId,Mat_no from Inventory--更新本期期初
update Account set Qty1=(select Qty4 from Account a where Cmonth=@cmonth1 and Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount1=(select Amount4 from Account a where Cmonth=@cmonth1 and Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth--更新入库
update Account set Price2=(select sum(Amount)/sum(Qty) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Qty2=(select sum(Qty) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount2=(select sum(Amount) from BillIn a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth--更新出库
update Account set Price3= (Amount1+Amount2)/(Qty1+Qty2) where Cmonth=@cmonth
update Account set Qty3=(select sum(Qty) from BillOut a where Cmonth=@cmonth Account.HId=a.HId and Account.Mat_no=a.Mat_no) where Cmonth=@cmonth
update Account set Amount3=Price3 * Qty3 --更新本期结存
update Account set Qty4=Qty1+Qty2-Qty3 where Cmonth=@cmonth
update Account set Amount4=Amount1+Amount2-Amount3 where Cmonth=@cmonth
update Account set Price4=Amount4/Qty4
以上是我一个解决方法,问题是运行效率太差,有没有更好的解决方案呢?求教!
a
set
Qty1=b.QTY4
from
Account a,
(select Qty4 from Account where Cmonth=@cmonth1 )b
where
Cmonth=@cmonth
and
a.HId=b.HId
and
at.Mat_no=b.Mat_no
如:
update Account set Qty4=Qty1+Qty2-Qty3 where Cmonth=@cmonth
update Account set Amount4=Amount1+Amount2-Amount3 where Cmonth=@cmonth
不就是:
update Account set Qty4=Qty1+Qty2-Qty3, Amount4=Amount1+Amount2-Amount3 where Cmonth=@cmonth
吗!