sql统计

--年龄统计
create proc RepEAge
as
select bms,total=count(bms),
a=sum(case when datediff(year,csrq,getdate())<16 then 1 else 0 end),
b=sum(case when datediff(year,csrq,getdate()) between 16 and 18 then 1 else 0 end),
c=sum(case when datediff(year,csrq,getdate()) between 19 and 25 then 1 else 0 end),
d=sum(case when datediff(year,csrq,getdate()) between 26 and 30 then 1 else 0 end),
e=sum(case when datediff(year,csrq,getdate()) between 31 and 40 then 1 else 0 end),
f=sum(case when datediff(year,csrq,getdate()) between 41 and 50 then 1 else 0 end),
g=sum(case when datediff(year,csrq,getdate()) between 51 and 60 then 1 else 0 end),
h=sum(case when 60<datediff(year,csrq,getdate()) then 1 else 0 end),
ageavg=sum(datediff(year,csrq,getdate()))/count(bms)
from dbo.HumanInfoText where bms<>'' and csrq<>'' group by bms
go

--婚姻状况统计
create proc RepMarry
as
select bms,total=count(bms),
marriedman=sum(case when married='已婚' and xb='男' then 1 else 0 end),
marriedwoman=sum(case when married='已婚' and xb='女' then 1 else 0 end),
unmarryman=sum(case when married='未婚' and xb='男' then 1 else 0 end),
unmarrywoman=sum(case when married='未婚' and xb='女' then 1 else 0 end)
from dbo.HumanInfoText where bms<>'' and married<>'' and xb<>'' group by bms
go

--政治面貌统计
create proc RepPolity
as
select bms,total=count(bms),
gd=sum(case when zzmm='党员' then 1 else 0 end),
gq=sum(case when zzmm='团员' then 1 else 0 end),
mz=sum(case when zzmm='民主党派' then 1 else 0 end),
qt=sum(case when zzmm='其它' then 1 else 0 end)
from dbo.HumanInfoText where bms<>'' and zzmm<>'' group by bms
go