fid, fqty, fsumqty-------------------------------------
1, 10, 12.30 //1的fsumqty初始值為12.30
2, 12, 24.30 //2的fsumqty=12.30+12=24.30
3, 8, 22.30 //3的fsumqty =24.30+8= 22.30
4.....
.
--第一条记录的fsumqty应该是10吧?create table tb ( fid int not null , fqty money null , fsumqty money null )insert tb ( fid , fqty , fsumqty ) values ( 1 , 10 , 12.3000 )
insert tb ( fid , fqty , fsumqty ) values ( 2 , 12 , 24.3000 )
insert tb ( fid , fqty , fsumqty ) values ( 3 , 8 , 32.3000 )--select * from tbselect identity(int,1,1) as AutoID, *
into #tp
from tbdrop table tb --select * from #tpselect A.fid, A.fqty,(select sum(fqty) from #tp where AutoID<=A.AutoID) as fsumqty
from #tp A
order by A.fiddrop table #tp
/*
--执行结果
fid fqty fsumqty
--------------------------------
1 10 10
2 12 22
3 8 30
*/
1, 10, 12.30 //1的fsumqty初始值為12.30
2, 12, 24.30 //2的fsumqty=12.30+12=24.30
3, 8, 22.30 //3的fsumqty =24.30+8= 22.30
4.....
.
--第一条记录的fsumqty应该是10吧?create table tb ( fid int not null , fqty money null , fsumqty money null )insert tb ( fid , fqty , fsumqty ) values ( 1 , 10 , 12.3000 )
insert tb ( fid , fqty , fsumqty ) values ( 2 , 12 , 24.3000 )
insert tb ( fid , fqty , fsumqty ) values ( 3 , 8 , 32.3000 )--select * from tbselect identity(int,1,1) as AutoID, *
into #tp
from tbdrop table tb --select * from #tpselect A.fid, A.fqty,(select sum(fqty) from #tp where AutoID<=A.AutoID) as fsumqty
from #tp A
order by A.fiddrop table #tp
/*
--执行结果
fid fqty fsumqty
--------------------------------
1 10 10
2 12 22
3 8 30
*/
回复人: wangtiecheng(cappuccino) ( ) 信誉:100
你的寫法給了我思路!
thanks all of you!
insert tb ( fid , fqty , fsumqty ) values ( 2 , 12 , 24.3000 )
insert tb ( fid , fqty , fsumqty ) values ( 3 , 8 , 32.3000 )--为了不影响原表的数据
select * into #1 from tb--更新字段fsumqty
declare @total money
select top 1 @total=isnull(fsumqty,0)-isnull(fqty,0) from #1
update #1 set @total=fsumqty=@total+fqtyselect * from #1--删除临时表
drop table #1
Thanks!