当循环到W3时数据不正确，自动将变量W1运算的数据加入到W3中?实际结果应该是：w1余：30,w2:8,w3:50,但程序却是w3:=10+50=60

Question

Declare  @StockID Int, 
         @ItemNo varchar(20),@InNumber Float,@InFare Float,@OutUnitPrice Float,@OutNumber Float,@OutFare Float,@LeaveNumber Float,
         @LeaveFare Float,@LastLeaveNumber Float,@LastLeaveFare Float,@StartDate Datetime,@EndDate DateTime,@ErrorInfo Int,@DayInt int  
     Set @ErrorInfo=0 
DECLARE SCStock_Cursor   Cursor for
SELECT  SCStock.ID  FROM SCStock  Where CalcDate>='2007-02-01' and CalcDate<='2007-02-28' 
OPEN    SCStock_Cursor 
FETCH NEXT FROM SCStock_Cursor INTO @StockID  
WHILE @@FETCH_STATUS=0 
  BEGIN
      ----------取出某品种的最大的结余数量、结余金额
       Select @LastLeaveNumber=IsNull(LeaveNumber,0),@LastLeaveFare=IsNull(LeaveFare,0)
       from SCStock Where SCStock.ID=(Select Max(SCStock.ID) from SCStock where ItemNo=(select ItemNo from SCStock where SCStock.ID=@StockID)  and IsCalcFlag=1 )
       Set @LastLeaveNumber=(select IsNULL(@LastLeaveNumber,0))
       set @LastLeaveFare=(select IsNULL(@LastLeaveFare,0)) 
     ------------------修改这一品种的这一次的结余数量、结余金额
       Update SCStock 
       Set IsCalcFlag=1,LeaveNumber=Convert(Decimal(18,2),IsNull((@LastLeaveNumber+InNumber-OutNumber),0)),
           LeaveFare=Convert(Decimal(18,6),IsNull((@LastLeaveFare+InFare-OutFare),0))
       Where SCStock.ID=@StockID 
       /*Set @ErrorInfo=@@Error 
       IF @ErrorInfo<>0 
          Goto ErrorInfoExit*/
       FETCH NEXT FROM SCStock_Cursor INTO  @StockID 
  END
CLOSE SCStock_Cursor
DEALLOCATE SCStock_Cursor   
表Scstock有几个字段ID int(4),ItemNo varchar(20),IsCalcFlag int 4,CalcDate datetime(8),InNumber float(8),InUnitPrice numeric(9),InFare numeric(9),OutNumber float(8),OutUnitPrice numeric(9),OutFare numeric(9),LeaveNumber float(8),LeaveUnitPrice numeric(9),LeaveFare numeric(9),PriceU char(6)
输入四组数据：ID,ItemNo,IsCalcFlag,CalcDate,InNumber,OutNumber,LeaverNumber
               1,W1    ,0         ,2007-02-22,10    ,0        ,0
               2,W2    ,0         ,2007-02-22,8     ,0        ,0
               3,W1    ,0         ,2007-02-22,20    ,0        ,0
               4,W3    ,0         ,2007-02-22,50    ,0        ,0

Answer 1

输入四组数据：
ID,ItemNo,IsCalcFlag,CalcDate,  InNumber,InFare,OutNumber,LeaverNumber,LeaverFare
1 ,W1    ,0         ,2007-02-21,10      ,80    ,0        ,0           ,0
2 ,W2    ,0         ,2007-02-21,8       ,72    ,0        ,0           ,0
3 ,W1    ,0         ,2007-02-21,20      ,100   ,0        ,0           ,0
4 ,W3    ,0         ,2007-02-21,50      ,500   ,0        ,0           ,0
想得到的结果是：
合计：W1 LeaverNumber=10+20=30,LeaverFare=80+100=180
      W2 LeaverNumber=8       ,LeaverFare=72
      W3 LeaverNumber=50      ,LeaverFare=500
但程序运行后的结果却是
合计：W1 LeaverNumber=10+20=30,LeaverFare=80+100=180
      W2 LeaverNumber=8       ,LeaverFare=72
      W3 LeaverNumber=10+50=60,LeaverFare=80+500=580
为何当W1再运算的时候系统自动将W1的@LastLeaveNumber一直保留呢？？？？请告知该程序如何修改才能得到想得到的结果呢？？？？?????

Answer 2

去掉Max(SCStock.ID)--表名 SCStock.
      Select @LastLeaveNumber=IsNull(LeaveNumber,0),@LastLeaveFare=IsNull(LeaveFare,0)
       from SCStock Where ID=
(Select Max(ID) from SCStock where ItemNo=(select ItemNo from SCStock where SCStock.ID=@StockID)  and IsCalcFlag=1 )

Answer 3

改一下这一段
 Select @LastLeaveNumber=
(select sum(LeaveNumber) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID),
@LastLeaveFare=(select sum(LeaveFare) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID)
 from SCStock tmp
 Where ID=@StockID

Answer 4

改一下更新这一段
update tmp
 Set LeaveNumber=
(select sum(InNumber) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID),
LeaveFare=(select sum(InFare) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID),
IsCalcFlag=1
from SCStock tmp
 Where ID=@StockID

Answer 5

create table SCStock(ID int,ItemNo varchar(2),IsCalcFlag int,CalcDate varchar(10), 
 InNumber int,InFare int,OutNumber int,LeaverNumber int,LeaverFare int)
insert SCStock select 1 ,'W1'   ,0         ,'2007-02-21',10      ,80    ,0        ,0           ,0
union all select 2 ,'W2'   ,0         ,'2007-02-21',8       ,72    ,0        ,0           ,0
union all select 3 ,'W1'    ,0         ,'2007-02-21',20      ,100   ,0        ,0           ,0
union all select 4 ,'W3'   ,0         ,'2007-02-21',50      ,500   ,0        ,0           ,0Declare  @StockID Int, 
         @ItemNo varchar(20),@InNumber Float,@InFare Float,@OutUnitPrice Float,@OutNumber Float,@OutFare Float,@LeaveNumber Float,
         --@LeaveFare Float,
           @LastLeaveNumber Float,
--@LastLeaveFare Float,
@StartDate Datetime,@EndDate DateTime,@ErrorInfo Int,@DayInt int  
     Set @ErrorInfo=0 
DECLARE SCStock_Cursor   Cursor for
SELECT  SCStock.ID  FROM SCStock  Where CalcDate>='2007-02-01' and CalcDate<='2007-02-28' 
OPEN    SCStock_Cursor 
FETCH NEXT FROM SCStock_Cursor INTO @StockID  
WHILE @@FETCH_STATUS=0 
  BEGIN
     update tmp
     Set LeaverNumber=
                     (select sum(InNumber) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID),
         LeaverFare=
                   (select sum(InFare) from SCStock where ItemNo=tmp.ItemNo and ID!>@StockID),
         IsCalcFlag=1
     from SCStock tmp
     Where ID=@StockID       FETCH NEXT FROM SCStock_Cursor INTO  @StockID 
  END
CLOSE SCStock_Cursor
DEALLOCATE SCStock_Cursor --查询结果:
select * from SCStock
ID          ItemNo IsCalcFlag  CalcDate   InNumber    InFare      OutNumber   LeaverNumber LeaverFare
----------- ------ ----------- ---------- ----------- ----------- ----------- ------------ -----------
1           W1     1           2007-02-21 10          80          0           10           80
2           W2     1           2007-02-21 8           72          0           8            72
3           W1     1           2007-02-21 20          100         0           30           180
4           W3     1           2007-02-21 50          500         0           50           500(4 行受影响)还是要以下结果:
create table SCStock(ID int,ItemNo varchar(2),IsCalcFlag int,CalcDate varchar(10), 
 InNumber int,InFare int,OutNumber int,LeaverNumber int,LeaverFare int)
insert SCStock select 1 ,'W1'   ,0         ,'2007-02-21',10      ,80    ,0        ,0           ,0
union all select 2 ,'W2'   ,0         ,'2007-02-21',8       ,72    ,0        ,0           ,0
union all select 3 ,'W1'    ,0         ,'2007-02-21',20      ,100   ,0        ,0           ,0
union all select 4 ,'W3'   ,0         ,'2007-02-21',50      ,500   ,0        ,0           ,0Declare  @StockID Int, 
         @ItemNo varchar(20),@InNumber Float,@InFare Float,@OutUnitPrice Float,@OutNumber Float,@OutFare Float,@LeaveNumber Float,
         --@LeaveFare Float,
           @LastLeaveNumber Float,
--@LastLeaveFare Float,
@StartDate Datetime,@EndDate DateTime,@ErrorInfo Int,@DayInt int  
     Set @ErrorInfo=0 
DECLARE SCStock_Cursor   Cursor for
SELECT  SCStock.ID  FROM SCStock  Where CalcDate>='2007-02-01' and CalcDate<='2007-02-28' 
OPEN    SCStock_Cursor 
FETCH NEXT FROM SCStock_Cursor INTO @StockID  
WHILE @@FETCH_STATUS=0 
  BEGIN
     update tmp
     Set LeaverNumber=
                     (select sum(InNumber) from SCStock where ItemNo=tmp.ItemNo ),
         LeaverFare=
                   (select sum(InFare) from SCStock where ItemNo=tmp.ItemNo ),
         IsCalcFlag=1
     from SCStock tmp
     Where ID=@StockID       FETCH NEXT FROM SCStock_Cursor INTO  @StockID 
  END
CLOSE SCStock_Cursor
DEALLOCATE SCStock_Cursor --查询结果:
select * from SCStockID          ItemNo IsCalcFlag  CalcDate   InNumber    InFare      OutNumber   LeaverNumber LeaverFare
----------- ------ ----------- ---------- ----------- ----------- ----------- ------------ -----------
1           W1     1           2007-02-21 10          80          0           30           180
2           W2     1           2007-02-21 8           72          0           8            72
3           W1     1           2007-02-21 20          100         0           30           180
4           W3     1           2007-02-21 50          500         0           50           500(4 行受影响)