typname part_id iodate month1 day1 iono inqty outqty total1 in_no remarks plastic_type
WGSI G901 00:00.0 9 21 CK02 2027 0 2027 0 0 ABC
WGSI G902 00:00.0 9 21 CK02 0 21 2006 0 0 PC
WGSI G901 00:00.0 9 22 CK04 3000 0 5006 0 0 ABC
WGSI G902 00:00.0 9 22 CK04 2 5 5003 0 0 PC
達到的目的就是要將查詢到的記錄的每筆的total1 為前一筆的total1 的值加上此筆記錄的inqty的值減去此筆記錄的outqty的值,依次類推。如上表,total1 =total1 (上一條記錄的)+inqty-outqty
但我下面的游標過程怎麼只能實現當前行數據相加減,但帶不了上一條記錄的total1
declare @typename varchar(10)
declare @part_id varchar(20)
declare @iodate datetime
declare @month1 int
declare @day1 int
declare @iono varchar(20)
declare @inqty numeric(18,0)
declare @outqty numeric(18,0)
declare @total1 numeric(18,0)
declare @in_no varchar(20)
declare @remarks varchar(10)
declare @summary varchar(20)
declare @plastic_type varchar(20)
--declare @mid numeric(18,0)
declare ccp1 cursor for
select typename,part_id,iodate,month1,day1,iono,inqty,outqty,total1,in_no,remarks,summary,plastic_type from #ccp order by iono,typename
open ccp1
fetch next from ccp1
into @typename,@part_id,@iodate,@month1,@day1,@iono,@inqty,@outqty,@total1,@in_no,@remarks,@summary,@plastic_type
while @@FETCH_STATUS = 0
begin
set @total1=@total1+@inqty-@outqty
--set @total1=@mid+@inqty-@outqty
-- @mid=@total1
insert into #ccp values ( @typename,@part_id,@iodate,@month1,@day1,@iono,@inqty,@outqty,@total1,@in_no,@remarks,@summary,@plastic_type)
fetch next from ccp1
into @typename,@part_id,@iodate,@month1,@day1,@iono,@inqty,@outqty,@total1,@in_no,@remarks,@summary,@plastic_type
end
close ccp1
deallocate ccp1
請知道怎麼解決的幫個忙,謝謝
[此贴子已经被作者于2007-10-23 16:47:46编辑过]