mysql 交叉表 存储过程_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