使用游标显示销售报表_使用游标和临时表

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