--用游标实现的累计金额问题
--声明2个变量
declare @id int
declare @num float
declare @num2 float
declare @sum float
CREATE TABLE [dbo].[a] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[num] [float] NULL ,
[num2] [float] NULL
) ON [PRIMARY]
GO
set @sum =0
--声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
declare mycursor cursor
for select [id],num,num2 from a
--打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @id,@num,@num2
--判断游标的状态
while (@@fetch_status=0)
begin
set @sum = @sum + @num
UPDATE A SET num2 = @sum where [id]=@id
--用游标去取下一条记录
fetch next from mycursor into @id,@num,@num2
end
close mycursor
deallocate mycursor
select [id],num,num2 from a
GO
--不用游标用查询
select [id],num,num2 into [#temp] from a
select [id],num,num2 = num + isnull((select sum([num]) from [#temp] where a.[id]>[id]),0) from a
drop table [#temp]
