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)
)
update #temp
set accidentPlan = b.accidentNum
from #temp a,accidentPlan b
where a.areaCode=b.areaCode and b.planYear=@totalYear
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 ;
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
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
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
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='合计'
where accidentPlan <>0 and areaName='合计'
update #temp set compareRate=accidentNum/lastYear
where lastYear <>0 and areaName='合计'
where lastYear <>0 and areaName='合计'
select areaCode,areaName,accidentPlan,accidentNum,deadNum,injuredNum,rate,lastYear,compareRate
from #temp order by areaCode
end