使用游标和临时表

create table TotalMileByMonth
(
TMonth char(8),
TMile numeric(10,0)
)
select * into #temp from
(select PM,PD,sum(M) as M from
(select SUBSTRING ( PD , 1 , 6 ) as PM,SUBSTRING ( PD , 1 , 4 )+'/'+SUBSTRING ( PD , 5 , 2 ) as PD ,M
from dbo.GCCM) a group by PM,[PD]) b

Declare @PM varchar(8)
Declare @TempMile numeric(10,0)
DECLARE My_CURSOR CURSOR FOR select PM from #temp
open My_CURSOR
FETCH NEXT FROM My_CURSOR INTO @PM
WHILE @@FETCH_STATUS = 0
BEGIN
select @TempMile=sum(M) from #temp where PM<= @PM
insert into TotalMileByMonth values(@PM,@TempMile)
FETCH NEXT FROM My_CURSOR INTO @PM
END
CLOSE My_CURSOR --关闭游标
DEALLOCATE My_CURSOR
select c.PM,c.PD,c.M,d.TMile from #temp c inner join TotalMileByMonth d
on c.PM=d.TMonth
drop table #temp
drop table TotalMileByMonth

转载于:https://www.cnblogs.com/whl4835349/p/5741183.html