--同一张表内、利用变化的上一行数据动态地修改下一行数据(示例Demo)
/*
CREATE TABLE Account (
[Month] [datetime] NOT NULL Prmary Key,
[PlanFare] [money] NULL ,
[PutoutFare] [money] NULL ,
[PlanPreFare] [money] NULL ,
[PutoutPreFare] [money] NULL
) --Account表生成脚本
*/
select identity(int,1,1) as [id],* into # from account order by [month] asc
CREATE TABLE #Temp (
[id] [int] NOT NULL,
[Month] [datetime] NOT NULL ,
[PlanFare] [money] NULL ,
[PutoutFare] [money] NULL ,
[PlanPreFare] [money] NULL ,
[PutoutPreFare] [money] NULL
)
declare @id int
declare m cursor for select [id] from # order by [id] asc
open m
fetch next from m into @id
while (@@fetch_status=0)
begin
insert into #Temp([id],[Month], PlanFare, PutoutFare, PlanPreFare, PutoutPreFare)
select [id],[Month], PlanFare, PutoutFare, PlanPreFare, PutoutPreFare from # where [id]=@id
UPDATE #
SET #.PlanPreFare = (a.PlanFare - a.putoutFare) + (a.PlanPreFare - a.putoutPreFare)
FROM #Temp AS a
WHERE #.[id] = a.[id]+1
--前行作依据,后行来修改(此次的后行,变成下次的前行,依此循环,直至表中的行遍历结束)
truncate table #Temp
fetch next from m into @id
end
close m
deallocate m
UPDATE Account
SET Account.PlanPreFare = a.PlanPreFare
FROM # AS a
WHERE Account.[Month] = a.[Month]
drop table #
drop table #Temp