create view zbfrfb
(xzqh,ct1,ct2,ct3,ct4,ct5)
as
select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh not in('612700','612600','610200','610500','610400',
'610300','610100','612500','612300')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh
UNION ALL
--榆林
select '612700' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('612700','612701')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) yulin
UNION ALL
--延安
select '612600' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('612600','612601')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) yanan
UNION ALL
--铜川
select '610200' xzqh, sum(ct1)ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('610200','610202','610203')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) tongchan
UNION ALL
--渭南
select '610500' xzqh, sum(ct1) ct1,sum(ct2)ct2,sum(ct3)ct3,sum(ct4)ct4,sum(ct5)ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('610500','610502')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) weinan
UNION ALL
--汉中
select '612300' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('612300','612301')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) hanzhong
UNION ALL
--商州市
select '612500' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('612500','612501')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) shangzhou
UNION ALL
--西安市
select '610100' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('610100','610102','610103','610104','610111','610112','610113','610114')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) xian
UNION ALL
--宝鸡市
select '610300' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('610303','610321','610300')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) baoji
UNION ALL
--咸阳市
select '610400' xzqh, sum(ct1) ct1,sum(ct2) ct2,sum(ct3) ct3,sum(ct4) ct4,sum(ct5) ct5 from (select xzqh,
sum( CASE WHEN (zczb>0 and zczb<=100) THEN 1 ELSE 0 END)ct1,
sum( CASE WHEN (zczb>100 and zczb<=500) THEN 1 ELSE 0 END)ct2,
sum( CASE WHEN (zczb>500 and zczb<=1000) THEN 1 ELSE 0 END)ct3,
sum( CASE WHEN (zczb>1000 and zczb<=100000000) THEN 1 ELSE 0 END)ct4,
sum( CASE WHEN (zczb>100000000) THEN 1 ELSE 0 END)ct5
from (
select b.zzjgdm ,b.zczb, b.xzqh from
(
select t.zzjgdm,t.xzqh,dj.zczb from
(select zzjgdm,xzqh from zzjgdmbz where xzqh in('610400','610402','610404','616900')) t left join QYFRDJ dj on t.zzjgdm=dj.zzjgdm
) b )a group by xzqh) xianyang