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,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一直保留呢????请告知该程序如何修改才能得到想得到的结果呢?????????
@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,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一直保留呢????请告知该程序如何修改才能得到想得到的结果呢?????????
(ID int ,
ItemNo varchar(20),
IsCalcFlag int,
CalcDate datetime,
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)
)
insert into scstock(id,ItemNo,CalcDate,InNumber,InFare)
select 1,'W1','2007-02-21',10,80
UNION ALL
select 2,'W2','2007-02-21',8,72
UNION ALL
select 3,'W1','2007-02-21',20,100
UNION ALL
select 4,'W3','2007-02-21',50,500
SELECT ItemNo,SUM(InNumber),SUM(InFare) FROM SCSTOCK
WHERE CalcDate>='2007-02-01' and CalcDate<='2007-02-28'
GROUP BY ItemNo
----------------------------
W1 30.0 180
W2 8.0 72
W3 50.0 500
这样?不用写你这么长吧
谢谢你的答复,可能我表达的不太清楚。我在数据库中已经建立了一个表,也输入了如下四组数据,
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
我想通过这个存储过程自动生成leavenumber,leavefare的值?
想得到的结果是:
合计: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一直保留呢????请告知该程序如何修改才能得到想得到的结果呢?????????
合计:W1 LeaverNumber=10+20=30,LeaverFare=80+100=180
W2 LeaverNumber=8 ,LeaverFare=72
W3 LeaverNumber=50 ,LeaverFare=500
请告知我段程序如何调整????
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 )
----这一段取出同一ItemNO里面最大ID的LeaveNumber和LastLeaveFare值,好像和你的要求不相符合呀
insert into #tmpTable select 'w1', 10, 80, 0, 0
union all select 'w2', 8, 72, 0, 0
union all select 'w1', 20, 100, 0, 0
union all select 'w3', 50, 500, 0, 0update #tmpTable set LeaverNumber = (select sum(t1.InNumber) from #tmpTable t1 where t1.a = #tmpTable.a group by t1.a)
, LeaverFare = (select sum(t1.InFare) from #tmpTable t1 where t1.a = #tmpTable.a group by t1.a)select a, max(LeaverNumber), max(LeaverFare) from #tmpTable group by a
ID,ItemNo,IsCalcFlag,CalcDate, InNumber,InFare,OutNumber,LeaverNumber,LeaverFare
1 ,W1 ,0 ,2007-02-21,10 ,80 ,0 ,10 ,80
2 ,W2 ,0 ,2007-02-21,8 ,72 ,0 ,8 ,72
3 ,W1 ,0 ,2007-02-21,20 ,100 ,0 ,30 ,180
4 ,W3 ,0 ,2007-02-21,50 ,500 ,0 ,50 ,500
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 ,20 ,180
4 ,W3 ,1 ,2007-02-21,50 ,500 ,0 ,50 ,500
LeaverFare = 同ItemNo的上一条InFare + 本记录的InFare?
是这个意思吗?
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)
@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 CREATE TABLE #TMP (itemno nvarchar(20),LeaveNumber int,LeaveFare int) DECLARE SCStock_Cursor Cursor for
SELECT SCStock.ID,itemNo FROM SCStock Where CalcDate>='2007-02-01' and CalcDate<='2007-02-28'
OPEN SCStock_Cursor
FETCH NEXT FROM SCStock_Cursor INTO @StockID,@ItemNo
WHILE @@FETCH_STATUS=0
BEGIN
declare @maxLeaveNumber int
declare @maxLeavefare int
select @maxLeaveNumber=isnull(max(LeaveNumber),0),@maxLeavefare = isNull(max(LeaveFare),0) from #tmp where itemno=@itemNo
Print cast(@maxLeaveNumber as varchar)+'/'+cast(@maxLeavefare as varchar)+'/'+@itemNo
--if exists(select itemno from #tmp where itemno=@itemno)
Update scstock set LeaveNumber = isnull(inNumber,0)+@maxLeaveNumber,leavefare=isnull(infare,0)+@maxLeavefare where id=@StockID
--else
-- Update scstock set LeaveNumber = inNumber+@maxLeaveNumber,leavefare=infare+@maxLeavefare
select @LeaveNumber=leavenumber,@LeaveFare=LeaveFare from scstock where id=@StockID insert into #tmp select @ItemNo,@LeaveNumber,@LeaveFare
FETCH NEXT FROM SCStock_Cursor INTO @StockID,@itemNo
END
CLOSE SCStock_Cursor
DEALLOCATE SCStock_Cursor drop table #tmp--select * from #tmp
Declare @StockID Int,
@ItemNo varchar(20),
@LeaveNumber Float,
@LeaveFare Float
CREATE TABLE #TMP (itemno nvarchar(20),LeaveNumber int,LeaveFare int)
DECLARE SCStock_Cursor Cursor for
SELECT SCStock.ID,itemNo FROM SCStock Where CalcDate>='2007-02-01' and CalcDate<='2007-02-28'
OPEN SCStock_Cursor
FETCH NEXT FROM SCStock_Cursor INTO @StockID,@ItemNo
WHILE @@FETCH_STATUS=0
BEGIN
declare @maxLeaveNumber int
declare @maxLeavefare int
select @maxLeaveNumber=isnull(max(LeaveNumber),0),@maxLeavefare = isNull(max(LeaveFare),0) from #tmp where itemno=@itemNo
Update scstock set LeaveNumber = isnull(inNumber,0)+@maxLeaveNumber,leavefare=isnull(infare,0)+@maxLeavefare where id=@StockID
select @LeaveNumber=leavenumber,@LeaveFare=LeaveFare from scstock where id=@StockID
insert into #tmp select @ItemNo,@LeaveNumber,@LeaveFare
FETCH NEXT FROM SCStock_Cursor INTO @StockID,@itemNo
END
CLOSE SCStock_Cursor
DEALLOCATE SCStock_Cursor
drop table #tmp
--------------------------------------
1 W1 NULL 2007-02-21 00:00:00.000 10.0 NULL 80 NULL NULL NULL 10.0 NULL 80 NULL
2 W2 NULL 2007-02-21 00:00:00.000 8.0 NULL 72 NULL NULL NULL 8.0 NULL 72 NULL
3 W1 NULL 2007-02-21 00:00:00.000 20.0 NULL 100 NULL NULL NULL 30.0 NULL 180 NULL
4 W3 NULL 2007-02-21 00:00:00.000 50.0 NULL 500 NULL NULL NULL 50.0 NULL 500 NULL
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
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
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 行受影响)