有这样一张表t_balance 余额表 fcustno为客户编号，fperiod为期间，fbal为余额。如果查询日期为2010-10-1到2011-02-12 之间的期初数，发生额，每期结存及最后余额。
第一步：先定义一个游标，目的是为了取客户
declare c_cust cursor for select fnumber from t_organization
open c_cust
fetch c_cust into @custno
while @@fetch_status = 0
begin
第二步：把期初数取过来
insert into #temp(fyear,fperiod,fdate,fcustno,fbal,fnote)
select fyear,fperiod,'期初数',fcustno,fbal,'c' from t_balance where [email protected]
fetch c_cust into @custno
end
close c_cust
deallocate c_cust
第三步：定义一个游标，目的是取出来查询起止日期所涉及的期间数
declare f_period cursor for select fyear,fperiod from t_perioddate where fstartdate>='2010-10-01' and fstartdate<'2011-02-12'
open f_period
fetch f_period into @f_year,@f_period
while @@fetch_status = 0
begin
第四步：取出发生额
insert into #temp(fyear,fperiod,fdate,fcustno,fbal,fnote)
select fyear,fperiod,fdate,fcustno,sum(fbal),'f' as fnote
from t_balance where [email protected]_year and [email protected]_period
group by fyear,fperiod,fdate,fcustno
这时查看一下插入了哪些数据：
select * from #temp where fnote='h' 时发现记录出现了五次同样数据
fetch f_period into @f_year,@f_period
end
close f_period
deallocate f_period