UC知道帮我分析一下这段游标代码!!急,谢了!
来源：百度知道 编辑：UC知道 时间：2024/06/07 01:24:45
create table #MonthSales(Sname varchar(20),Mon01 decimal(6,2),Mon02 decimal(6,2),Mon03 decimal(6,2),
Mon04 decimal(6,2),Mon05 decimal(6,2),Mon06 decimal(6,2),Mon07 decimal(6,2),
Mon08 decimal(6,2),Mon09 decimal(6,2),Mon10 decimal(6,2),Mon11 decimal(6,2),
Mon12 decimal(6,2))
insert #MonthSales
select Sname,0,0,0,0,0,0,0,0,0,0,0,0 from Store
declare @Sname varchar(20),@Mon char(4),@Mon_sales decimal(6,2),@update varchar(256)
declare MonthSales_cur cursor global scroll for
select Sales.Sname,
DATENAME(mm,Saledate) as Mon,
sum(Price*Amount) as Mon_sales
from Sales inner join Book on Sales.Sname=Book.Sname and Sales.BookID=Book.BookID
group by Sales.Sname,DATENAME(mm,Saledate)
order by Sales.Sname
open MonthSales_cur
fetch next from MonthSales_cur
into @Sname,@Mon,@Mon_sales
Mon04 decimal(6,2),Mon05 decimal(6,2),Mon06 decimal(6,2),Mon07 decimal(6,2),
Mon08 decimal(6,2),Mon09 decimal(6,2),Mon10 decimal(6,2),Mon11 decimal(6,2),
Mon12 decimal(6,2))
insert #MonthSales
select Sname,0,0,0,0,0,0,0,0,0,0,0,0 from Store
declare @Sname varchar(20),@Mon char(4),@Mon_sales decimal(6,2),@update varchar(256)
declare MonthSales_cur cursor global scroll for
select Sales.Sname,
DATENAME(mm,Saledate) as Mon,
sum(Price*Amount) as Mon_sales
from Sales inner join Book on Sales.Sname=Book.Sname and Sales.BookID=Book.BookID
group by Sales.Sname,DATENAME(mm,Saledate)
order by Sales.Sname
open MonthSales_cur
fetch next from MonthSales_cur
into @Sname,@Mon,@Mon_sales
我看看!
首先建表,
插表头,
定义变量
定义游标其中global scroll 大致就是全局滚动的游标,全局性的
打开游标
直到 while @@FETCH_STATUS = 0
begin
set @update='update #MonthSales set Mon'+@Mon+'='+convert(char, @Mon_sales)+' where Sname='''+@Sname+''''
exec (@update)
fetch next from MonthSales_cur
into @Sname,@Mon,@Mon_sales
end
设置显示格式,通过变量update实现
close MonthSales_cur
deallocate MonthSales_cur
关闭游标,及相关联