mysql 游标计算累计金额_用游标实现的累计金额问题 | 学步园

--用游标实现的累计金额问题

--声明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]