SQL 交叉表存储过程

 

SQL存储过程，主要产生交叉表，语句如下：

ALTER        PROCEDURE accident_report
   @areaCode varchar(20), --地区编码
   @totalYear int   --统计年度
AS
declare 
   @arealen int ,
   @areaCodeLike varchar(20) ,
   @thisRepMonth int
begin
create table #temp
(
 areaCode varchar(10),
 areaName varchar(30),
 accidentPlan int ,
 accidentNum int ,
 deadNum int,
 injuredNum int,
 rate decimal(10,4),
 lastYear int,
 compareRate decimal(10,4)
 )

insert into #temp
(areaCode,areaName )
select id,name from jaf_region where parentID=@areaCode

update #temp
set accidentPlan = b.accidentNum
from #temp a,accidentPlan b
where a.areaCode=b.areaCode and b.planYear=@totalYear

set @arealen = len(@areaCode)

set @areaCodeLike = @areaCode+'%'

select @thisRepMonth = max(repMonth )
from repAccident where repYear=@totalYear ;

update #temp set
accidentNum=b.accidentNum,
deadNum=b.deadNum,
injuredNum=b.injuredNum
from #temp ,(
select substring(place,1,@arealen+2) areaCode,count(id) accidentNum,sum(totalDead) deadNum ,
sum(totalGBH) injuredNum
from repaccident where repYear=@totalYear and place like @areaCodeLike
group by substring(place,1,@arealen+2)) b
where #temp.areaCode=b.areaCode

update #temp set
lastYear=b.accidentNum
from #temp ,(
select substring(place,1,@arealen+2) areaCode,count(id) accidentNum
from repaccident where repYear=@totalYear - 1 and repMonth <=@thisRepMonth
 and place like @areaCodeLike
group by substring(place,1,@arealen+2)) b
where #temp.areaCode=b.areaCode

update #temp set rate = accidentNum / accidentPlan where accidentPlan <>0
update #temp set compareRate = accidentNum / lastYear where lastYear <>0
update #temp set accidentPlan= 0 where accidentPlan is null
update #temp set accidentNum= 0 where accidentNum is null
update #temp set deadNum= 0 where deadNum is null
update #temp set injuredNum= 0 where injuredNum is null
update #temp set rate= 0 where rate is null
update #temp set lastYear= 0 where lastYear is null
update #temp set compareRate= 0 where compareRate is null

insert into #temp(areaCode,areaName,accidentPlan,accidentNum,deadNum,injuredNum,rate,lastYear,compareRate)
select '9999','合计',sum(accidentPlan),sum(accidentNum),sum(deadNum),sum(injuredNum),0,sum(lastYear),0
from #temp

update #temp set rate=accidentNum/accidentPlan
where accidentPlan <>0 and areaName='合计'

update #temp set compareRate=accidentNum/lastYear
where lastYear <>0 and areaName='合计'

select areaCode,areaName,accidentPlan,accidentNum,deadNum,injuredNum,rate,lastYear,compareRate

from #temp order by areaCode

end