紧急求助：用游标取数来更新某一列的问题
如果来实现这种功能，第一行余额为37000,第二行的余额＝37000＋收款0－发出金额9000＝28000,也就是余额是累加的。我写了一个存储过程，取出来的数不对，是把临时表里取到的所有的客户的余额都累加了。还望高手指点啊。
我的联系方式：QQ：26978895 TEL:18957719082
set nocount on
create table #balancetemp
create index index_baltemp on #balancetemp(fdate,fdeptid,fcustid)
declare @fdate char(10)
declare @fstarttime char(10)
declare @fendtime char(10)
declare @fyear char(4)
declare @fperiod varchar(2)
declare @sumamount decimal(28,10)
declare @fkthamount decimal(28,10)
declare @fentryid int
declare @fitem varchar(100)
declare @fdept varchar(100)
declare @fcust varchar(100)
declare @bal decimal(28,10)
select @fentryid=0
select @sumamount=0
select @fkthamount=0
--select * from t_systemprofile Where FCategory = 'gl'
Select @fyear =FValue From t_SystemProfile Where FCategory = 'gl' And FKey = 'fstartYear'
select @fperiod =FValue From t_SystemProfile Where FCategory = 'gl' And FKey = 'fstartperiod'
--定义游标,取出客户
declare @cust int
declare @dept int
declare cb_cust cursor for select f1,f2 from t_itemdetail where fdetailid in (select fdetailid from t_itemdetailv ) and fdetailcount=2 and f1>0 and f2>0
order by f1,f2
OPEN cb_cust
FETCH NEXT FROM cb_cust
INTO @cust,@dept
WHILE @@FETCH_STATUS = 0
begin
--先取出当前期间的期初数
insert into #balancetemp(fdate,fdeptid,fdept,fcustid,fcust,fbegbal,frecamount,ffyamount,fqtamount,fitemname,fthqty,
fthprice,foutamount,fkpqty,fkpprice,fkpamount,fwkpqty,fwkpprice,fwkpamount,fkthamount,fnote)
select '' as fdate,f2.fdeptid,f2.fdeptname, fcustid,f1.fcustname,sum(tb.fbeginbalance),0,0 ,0,'',0,0,0,0,0,0,0,0,0,0,'C'as fnote from t_balance tb
inner join (SELECT FDetailID ,b.FNumber AS F1Number ,b.fname as fdeptname,b.fitemid as fdeptid,b.FNumber+'-'+b.fname F2 ,b.FLevel F1Level ,b.FItemClassID F1ClassID From
t_ItemDetail a , t_Item b WHERE a.F2 = b.FItemID AND b.FItemID <>0 ) F2 ON F2.FDetailID =tb.FDetailID and tb.fcurrencyid=1
INNER JOIN
( SELECT FDetailID ,b.FNumber AS F4Number ,b.fname as fcustname,b.fitemid as fcustid,b.FNumber+'-'+b.fname F1 ,b.FLevel F4Level ,b.FItemClassID F4ClassID From t_ItemDetail a , t_Item b WHERE a.F1 = b.FItemID AND
b.FItemID <>0 ) F1 ON F1.FDetailID =tb.FDetailID
and tb.faccountid in
(select faccountid from t_account where fnumber like '1400%')
where -- fcustNAME='上海国精商贸有限公司' and fdeptNAME='上海营销中心' and fcurrencyid=1 and fperiod='9' --and fnote='C'
fyear='2010' and fperiod='9' and fcurrencyid=1 and f2.fdeptid=@dept and f1.fcustid=@cust
group by f2.fdeptid,f2.fdeptname,fcustid,f1.fcustname
--插入收款金额
insert into #balancetemp(fdate,fdeptid,fdept,fcustid,fcust,fbegbal,frecamount,ffyamount,fqtamount,fitemname,fthqty,
fthprice,foutamount,fkpqty,fkpprice,fkpamount,fwkpqty,fwkpprice,fwkpamount,fkthamount,fnote)
SELECT convert(char(10),tb_show.fpdate,121)as fdate,f2.fdeptid,F2.fdept,f1.f1fitemid fcustid,f1.fcust,0 as fbegbal,
Sum(ISNULL(Famount,0)*(1-Fdc)) frecamount,0 as ffyamount,0 as fqtamount,''as fitemname,0 as fthqty,0 as fthprice,
0 as foutamount,0 as fkpqty,0 as fkpprice ,0 as fkpamount,0 as fwkpqty,0 as fwkpprice,0 as fwkpamount,
0 as fkthamount,'S' AS fnote
FROM ( SELECT
t_VoucherEntry.FAmount FAmount , t_VoucherEntry.Fdc,a.Fdc adc,a.FNumber AS FAcctNumber,a.FName AS FAcctName, t_VoucherEntry.FDetailID,
a.FLevel AS FAcctLevel, t_voucher.fdate as fpdate FROM (Select
FBrNo,FvoucherID,FDate,FYear,FPeriod,
FGroupID,FNumber,FReference,FExplanation,FAttachments,
FEntryCount,FDebitTotal,FCreditTotal,FInternalInd,FChecked,
FPosted,FPreparerID,FCheckerID,FPosterID,FCashierID,
FHandler,FOwnerGroupID,FObjectName,FParameter,FSerialNum,
FTranType,FTransDate,FFrameWorkID,FApproveID,FFootNote,
UUID , FMODIFYTIME
From t_Voucher
Union All
Select
FBrNo,FvoucherID,FDate,FYear,FPeriod,
FGroupID,FNumber,FReference,FExplanation,FAttachments,
FEntryCount,FDebitTotal,FCreditTotal,FInternalInd,FChecked,
FPosted,FPreparerID,FCheckerID,FPosterID,FCashierID,
FHandler,FOwnerGroupID,FObjectName,FParameter,FSerialNum,
FTranType,FTransDate,FFrameWorkID,FApproveID,FFootNote,
UUID , FMODIFYTIME
From t_VoucherAdjust) t_Voucher ,t_VoucherEntry as t_VoucherEntry ,t_Account a , t_VoucherGroup WHERE
t_VoucherEntry.fdetailid<>0 and t_Voucher.fvoucherid=t_VoucherEntry.fvoucherid and
t_VoucherEntry.faccountid=a.faccountid and t_VoucherGroup.Fgroupid=t_Voucher.Fgroupid AND t_VoucherEntry.FcurrencyId=1 AND a.FDelete<>1
AND t_Voucher.FPosted>=0
AND (( a.FNumber like '1131%'))
) tb_Show
INNER JOIN ( SELECT FDetailID ,B.Fname as fcust, b.FNumber AS F1Number ,b.fitemid as f1fitemid,b.FNumber+'-'+b.fname F1 ,b.FLevel F1Level ,b.FItemClassID F1ClassID From
t_ItemDetail a , t_Item b WHERE a.F1 = b.FItemID AND b.FItemID <>0 ) F1 ON F1.FDetailID =tb_show.FDetailID INNER JOIN
( SELECT FDetailID ,b.FNumber AS F4Number ,b.fname as fdept ,b.fitemid as fdeptid ,b.FNumber+'-'+b.fname F2 ,b.FLevel F4Level ,b.FItemClassID F4ClassID From t_ItemDetail a , t_Item b WHERE a.F2 = b.FItemID AND
b.FItemID <>0 ) F2 ON F2.FDetailID =tb_show.FDetailID
and f2.fdeptid=@dept and f1.f1fitemid=@cust
Group by tb_show.fpdate,f2.fdeptid, F2.fdept,f1.f1fitemid,f1.fcust
--插入开始期间到查询日期为止的提货金额
insert into #balancetemp(fdate,fdeptid,fdept,fcustid,fcust,fbegbal,frecamount,ffyamount,fqtamount,fitemname,fthqty,fthprice,foutamount,fkpqty,fkpprice,fkpamount,fwkpqty,fwkpprice,fwkpamount,fkthamount,fnote)
select convert(char(10),v1.Fdate,121) AS Fdate,v1.fdeptid,t11.fname as fdept,t4.fitemid fcustid,t4.FName AS FCust,0 as fbegbal,0 as frecamount,0 as ffyamount,
0 as fqtamount,'' as fitemname,sum(u1.fqty) fthqty,u1.FConsignPrice fthprice,sum(u1.FConsignAmount) AS Foutamount,
0 as fkpqty,0 as fkpprice,0 as fkpamount,0 as fwkpqty,0 as fwkpprice,0 as fwkpamount,0 as fkthamount,'T'AS FNOTE
from icStockbill v1 INNER JOIN icstockbillEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID <>0
inner join t_icitem ti on u1.fitemid=ti.fitemid
INNER JOIN t_Organization t4 ON v1.fsupplyid = t4.FItemID AND t4.FItemID <>0
-- LEFT OUTER JOIN t_Stock t9 ON u1.FStockID = t9.FItemID AND t9.FItemID <>0
LEFT OUTER JOIN t_Department t11 ON v1.FDeptID = t11.FItemID AND t11.FItemID <>0
LEFT OUTER JOIN t_Emp t12 ON v1.FEmpID = t12.FItemID AND t12.FItemID <>0
where v1.fdeptid=@dept and v1.fsupplyid=@cust and v1.fdate>='2010-09-01'
group by v1.fdate,v1.fdeptid,t11.fname,t4.fitemid,t4.fname,ti.fname,u1.FConsignPrice
-----得到合计可提货金额
insert into #balancetemp(fdate,fdeptid,fdept,fcustid,fcust,fbegbal,frecamount,fitemname,fthqty,foutamount,fkthamount,fnote)
select fdate,fdeptid,fdept,fcustid,fcust,sum(fbegbal),sum(frecamount),fitemname,sum(fthqty),sum(foutamount),(sum(fbegbal)+sum(frecamount)-sum(foutamount)+sum(fkthamount))as fkthamount,'h'
from #balancetemp where fdeptid=@dept and fcustid = @cust
group by fdate,fdeptid,fdept,fcustid,fcust,fitemname
fetch cb_cust into @cust,@dept
end
close cb_cust
deallocate cb_cust
declare @i int
declare @fdept2 int
declare @fcust2 int
declare @frecamount decimal(28,10)
declare @foutamount decimal(28,10)
declare @fbegbal decimal(28,10)
select @fkthamount=0
--这里要写一个游标来更新fkthamount这一列
declare cust_kth cursor for select fcustid,fdeptid ,fitemname,fdate from #balancetemp where fnote='h'and fcust like'%%' and fdept like'%%'
group by fdeptid,fcustid,fitemname,fdate --
order by fdeptid,fcustid,fitemname,fdate
open cust_kth
fetch next from cust_kth into @fcust2 ,@fdept2 ,@fitem,@fdate
WHILE @@FETCH_STATUS = 0
begin
--select count(fdate),fcust from #balancetemp where fcust='江苏南通鑫兔贸易有限公司' and fnote='h' group by fcust
select @i=count(fdate) from #balancetemp where fdeptid=@fdept2 and fcustid = @fcust2 and fnote='h'
-- select * from #balancetemp where fdeptid=@fdept2 and fcustid = @fcust2 and fnote='c'
--select fbegbal,fkthamount,fcust from #balancetemp where fdeptid=@fdept2 and fcustid = @fcust2 and fitemname=@fitem and fdate=@fdate and fnote='h' and fcust like'%江苏南通鑫兔贸易有限公司%'
select @bal=fbegbal,@frecamount=frecamount,@foutamount=foutamount from #balancetemp where fdeptid=@fdept2 and fcustid = @fcust2 and fitemname=@fitem and fdate=@fdate and fnote='h'
if @fbegbal<>0
begin
select @fkthamount=@bal+@fkthamount+@frecamount-@foutamount
--else
end
select @fkthamount=@bal+@fkthamount+@frecamount-@foutamount
update #balancetemp
set fkthamount=@fkthamount,fnote='d'
where fdeptid=@fdept2 and fcustid = @fcust2 and fitemname=@fitem and fdate=@fdate--and -fdate=@fdate
and fnote='h'
--
fetch next from cust_kth into @fcust2,@fdept2,@fitem,@fdate
end
close cust_kth
deallocate cust_kth
--发生额
select fdate,ba.fdept,ba.fcust,sum(fbegbal),sum(frecamount),fitemname,sum(foutamount) as foutamount,sum(fthqty) as fthqty,sum(fkthamount)
from #balancetemp ba inner join
t_department td on ba.fdept=td.fname
left outer join t_organization tor on tor.fname=ba.fcust
where ba.fnote IN ('d')
and (td.fname like'%南京营销中心%' AND
tor.fname like '%%'
)
group by fdept,ba.fcust,fitemname,fdate
order by fdept,fcust,fitemname,fdate
truncate table #balancetemp
drop table #balancetemp
set nocount off
[解决办法]
有没有列（或者id）能判断这条记录是第几行？比如第一行id就是1，第二行的id是2或者一定比1大？依此类推，如果不能那可以加一个标识列，我感觉从你的“如果来实现这种功能，第一行余额为37000,第二行的余额＝37000＋收款0－发出金额9000＝28000,也就是余额是累加的。”这段话的描述来说不需要使用游标（当然是我前面说的情况成立），用关联子查询
update tb set tb.result = (select sum(b.收款-b.发出金额) where b.id >= tb.id)
大概就是这么个意思。
[解决办法]
不需要用游标处理
select 1 as 顺序,'2010-08-31' as 日期,'A' as 产品,100 as 入数量,0 as 出数量,null as 结存数 --期初，入库数量是100
into #tb
union all
select 2 as 顺序,'2010-09-1' as 日期,'A' as 产品,200 as 入数量,0 as 出数量,null as 结存数
union all
select 3 as 顺序,'2010-09-1' as 日期,'A' as 产品,0 as 入数量,50 as 出数量,null as 结存数
union all
select 4 as 顺序,'2010-09-2' as 日期,'A' as 产品,0 as 入数量,60 as 出数量,null as 结存数
union all
select 5 as 顺序,'2010-08-31' as 日期,'B' as 产品,200 as 入数量,0 as 出数量,null as 结存数 --期初，入库数量是200
union all
select 6 as 顺序,'2010-09-1' as 日期,'B' as 产品,0 as 入数量,100 as 出数量,null as 结存数
union all
select 7 as 顺序,'2010-09-5' as 日期,'B' as 产品,0 as 入数量,50 as 出数量,null as 结存数
update #tb
set 结存数 = (select sum(a.入数量-a.出数量) from #tb a where a.产品=#tb.产品 and a.日期<=#tb.日期 and a.顺序<=#tb.顺序)
select *
from #tb
drop table #tb
/*
顺序 日期 产品 入数量 出数量 结存数
----------- ---------- ---- ----------- ----------- -----------
1 2010-08-31 A 100 0 100
2 2010-09-1 A 200 0 300
3 2010-09-1 A 0 50 250
4 2010-09-2 A 0 60 190
5 2010-08-31 B 200 0 200
6 2010-09-1 B 0 100 100
7 2010-09-5 B 0 50 50
*/
