select id into p_sid from lbOrganization where name='p_klmy';
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL (qMC, ZRS, CZJM, LDRK, DBZRS, DBZHS, DSRZRS, DSRZHS, WQGZRS, WQGZJE, YLZRS, YLZJE, LLZRS, GLLR) select b.name, count(a.f4), '', '', '', '', '', '', '', '', '', '', '', '' from JC_JMXX a, lbOrganization b where a.f142683 = b.id
group by b.name; commit; -----------------------------------------提取克拉玛依市长住人口流动人口 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set CZJM = (select count(a.f4) count from JC_JMXX a, lbOrganization b where a.f142683 = b.id and a.f62 = 1
and b.name = c.qmc ); update SWSQ_ZRS_HJ_ZFJZ_YL_LL e set ldrk = (select ldrk from (select b.name name, count(a.f4) ldrk from JC_JMXX a, lbOrganization b where a.f142683 = b.id and a.f62 <> 1
group by b.name) f where e.qmc = f.name); ---------------------------------------------提取克拉玛依市低保人数\户数 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set dbzrs = (select count(a.f4) count from jc_jmxx a, lbOrganization b where a.f142683 = b.id and a.f68 <= 260 and a.f62 = 1
and c.qmc=b.name) ;
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DBZHS = ( select count(jzjt) count from jc_jmxx a, lbOrganization b where a.f142683 = b.id and a.f68 <= 260 and a.f62 = 1
and c.qmc = b.name); commit; ---------------------------------------------统计克拉玛依市低收入总人数\总户数 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DSRZRS = (select count(*) from (select b.name name, '', '', count(a.f13), '' from T_zfjz_bzjt a, lbOrganization b where a.q = b.id and a.zt = 2
group by name) d where c.qmc = d.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DSRZHS = (select count(*) from (select b.name name, count(f16) from T_zfjz_bzjt a, lbOrganization b where a.zt = 2
and a.q = b.id group by b.name) d where c.qmc = d.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL a--------------------调整合计 set xh =(select xh from (select smc,rownum xh from SWSQ_dbh) b where a.smc=b.smc );
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL(qMC, ZRS, CZJM, LDRK, DBZRS, DBZHS, DSRZRS, DSRZHS, WQGZRS, WQGZJE, YLZRS, YLZJE, LLZRS, GLLR,xh) select '合计',sum(zrs),sum(CZJM), sum(LDRK), sum(DBZRS), sum(DBZHS), sum(DSRZRS), sum(DSRZHS), sum(WQGZRS), sum(WQGZJE), sum(YLZRS), sum(YLZJE), sum(LLZRS), sum(GLLR),'999' from SWSQ_ZRS_HJ_ZFJZ_YL_LL order by zrs; open c for select QMC 区, ZRS 总人数, CZJM 常住居民, LDRK 流动人口, DBZRS 低保户总人数, DBZHS 低保户总户数, DSRZRS 低收入总人数, DSRZHS 低收入总户数, WQGZRS 五七工总人数, WQGZJE 五七工总金额, YLZRS 医疗救助总人数, YLZJE 医疗救助总金额, LLZRS 老龄事物总人数, GLLR 高龄老人 from SWSQ_ZRS_HJ_ZFJZ_YL_LL order by zrs desc; /*end if;*/
--------------------------------提取克拉玛依区全部人数 /* if p_klmy is not null then*/ /* select id into p_qid from lbOrganization where name= p_klmy;*/ insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL (jdmc, ZRS, CZJM, LDRK, DBZRS, DBZHS, DSRZRS, DSRZHS, WQGZRS, WQGZJE, YLZRS, YLZJE, LLZRS, GLLR) select b.name, count(a.f4), '', '', '', '', '', '', '', '', '', '', '', '' from JC_JMXX a,lbOrganization b where a.f142683 = p_qid and a.f7 = b.id group by b.name;
commit; -----------------------------------提取克拉玛依市各区长住人口流动人口 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set czjm = (select count(a.f4) count from JC_JMXX a, lbOrganization b where a.f142683 = p_qid and a.f7 = b.id and a.f62 = 1 and c.jdmc = b.name );
update SWSQ_ZRS_HJ_ZFJZ_YL_LL e set ldrk = (select count(a.f4) count from JC_JMXX a, lbOrganization b where a.f142683 = p_qid and a.f7 = b.id and a.f62 <> 1 and e.jdmc = b.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL e set DBZRS=(select count(a.f4) dbzrs from jc_jmxx a, lbOrganization b where a.f142683 = p_qid and a.f7=b.id and a.f68 <= 260 and a.f62 = 1 and e.jdmc=b.name );
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DBZHS=(select count(jzjt) count from jc_jmxx a, lbOrganization b where a.f142683 = p_qid and a.f7=b.id and a.f68 <= 260 and a.f62 = 1 and c.jdmc= b.name ); --------------------------------提取克拉玛依市各区低收入总人数户数 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DSRZRS = (select count(a.f13) count from T_zfjz_bzjt a, lbOrganization b where a.q = p_qid and a.jd=b.id and a.zt =2 and c.jdmc = b.name); update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DSRZHS = (select count(f16) count from T_zfjz_bzjt a, lbOrganization b where a.zt = 2 and a.q = p_qid and a.jd=b.id and c.jdmc = b.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL a--------------------调整合计 set xh =(select xh from (select smc,rownum xh from SWSQ_dbh) b where a.smc=b.smc );
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL
(qMC,
ZRS,
CZJM,
LDRK,
DBZRS,
DBZHS,
DSRZRS,
DSRZHS,
WQGZRS,
WQGZJE,
YLZRS,
YLZJE,
LLZRS,
GLLR)
select b.name,
count(a.f4),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
from JC_JMXX a, lbOrganization b
where a.f142683 = b.id
group by b.name;
commit;
-----------------------------------------提取克拉玛依市长住人口流动人口
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set CZJM =
(select count(a.f4) count
from JC_JMXX a, lbOrganization b
where a.f142683 = b.id
and a.f62 = 1
and b.name = c.qmc ); update SWSQ_ZRS_HJ_ZFJZ_YL_LL e
set ldrk =
(select ldrk
from (select b.name name, count(a.f4) ldrk
from JC_JMXX a, lbOrganization b
where a.f142683 = b.id
and a.f62 <> 1
group by b.name) f
where e.qmc = f.name); ---------------------------------------------提取克拉玛依市低保人数\户数
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set dbzrs =
(select count(a.f4) count
from jc_jmxx a, lbOrganization b
where a.f142683 = b.id
and a.f68 <= 260
and a.f62 = 1
and c.qmc=b.name) ;
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set DBZHS =
( select count(jzjt) count
from jc_jmxx a, lbOrganization b
where a.f142683 = b.id
and a.f68 <= 260
and a.f62 = 1
and c.qmc = b.name);
commit;
---------------------------------------------统计克拉玛依市低收入总人数\总户数
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set DSRZRS =
(select count(*)
from (select b.name name, '', '', count(a.f13), ''
from T_zfjz_bzjt a, lbOrganization b
where a.q = b.id
and a.zt = 2
group by name) d
where c.qmc = d.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set DSRZHS =
(select count(*)
from (select b.name name, count(f16)
from T_zfjz_bzjt a, lbOrganization b
where a.zt = 2
and a.q = b.id
group by b.name) d
where c.qmc = d.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL a--------------------调整合计
set xh =(select xh from (select smc,rownum xh from SWSQ_dbh) b
where a.smc=b.smc );
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL(qMC,
ZRS,
CZJM,
LDRK,
DBZRS,
DBZHS,
DSRZRS,
DSRZHS,
WQGZRS,
WQGZJE,
YLZRS,
YLZJE,
LLZRS,
GLLR,xh)
select '合计',sum(zrs),sum(CZJM),
sum(LDRK),
sum(DBZRS),
sum(DBZHS),
sum(DSRZRS),
sum(DSRZHS),
sum(WQGZRS),
sum(WQGZJE),
sum(YLZRS),
sum(YLZJE),
sum(LLZRS),
sum(GLLR),'999' from SWSQ_ZRS_HJ_ZFJZ_YL_LL order by zrs;
open c for
select QMC 区,
ZRS 总人数,
CZJM 常住居民,
LDRK 流动人口,
DBZRS 低保户总人数,
DBZHS 低保户总户数,
DSRZRS 低收入总人数,
DSRZHS 低收入总户数,
WQGZRS 五七工总人数,
WQGZJE 五七工总金额,
YLZRS 医疗救助总人数,
YLZJE 医疗救助总金额,
LLZRS 老龄事物总人数,
GLLR 高龄老人
from SWSQ_ZRS_HJ_ZFJZ_YL_LL order by zrs desc;
/*end if;*/
--------------------------------提取克拉玛依区全部人数
/* if p_klmy is not null then*/
/* select id into p_qid from lbOrganization where name= p_klmy;*/
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL
(jdmc,
ZRS,
CZJM,
LDRK,
DBZRS,
DBZHS,
DSRZRS,
DSRZHS,
WQGZRS,
WQGZJE,
YLZRS,
YLZJE,
LLZRS,
GLLR)
select b.name,
count(a.f4),
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'' from JC_JMXX a,lbOrganization b
where a.f142683 = p_qid and a.f7 = b.id
group by b.name;
commit;
-----------------------------------提取克拉玛依市各区长住人口流动人口
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set czjm =
(select count(a.f4) count
from JC_JMXX a, lbOrganization b
where a.f142683 = p_qid
and a.f7 = b.id
and a.f62 = 1
and c.jdmc = b.name
);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL e
set ldrk =
(select count(a.f4) count
from JC_JMXX a, lbOrganization b
where a.f142683 = p_qid
and a.f7 = b.id
and a.f62 <> 1
and e.jdmc = b.name);
----------------------------------------------提取克拉玛依市各区低保人数\户数
update SWSQ_ZRS_HJ_ZFJZ_YL_LL e
set DBZRS=(select count(a.f4) dbzrs
from jc_jmxx a, lbOrganization b
where a.f142683 = p_qid
and a.f7=b.id
and a.f68 <= 260
and a.f62 = 1
and e.jdmc=b.name );
update SWSQ_ZRS_HJ_ZFJZ_YL_LL c set DBZHS=(select count(jzjt) count
from jc_jmxx a, lbOrganization b
where a.f142683 = p_qid
and a.f7=b.id
and a.f68 <= 260
and a.f62 = 1
and c.jdmc= b.name );
--------------------------------提取克拉玛依市各区低收入总人数户数 update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set DSRZRS =
(select count(a.f13) count
from T_zfjz_bzjt a, lbOrganization b
where a.q = p_qid
and a.jd=b.id
and a.zt =2
and c.jdmc = b.name); update SWSQ_ZRS_HJ_ZFJZ_YL_LL c
set DSRZHS =
(select count(f16) count
from T_zfjz_bzjt a, lbOrganization b
where a.zt = 2
and a.q = p_qid
and a.jd=b.id
and c.jdmc = b.name);
update SWSQ_ZRS_HJ_ZFJZ_YL_LL a--------------------调整合计
set xh =(select xh from (select smc,rownum xh from SWSQ_dbh) b
where a.smc=b.smc );
insert into SWSQ_ZRS_HJ_ZFJZ_YL_LL(jdmc,
ZRS,
CZJM,
LDRK,
DBZRS,
DBZHS,
DSRZRS,
DSRZHS,
WQGZRS,
WQGZJE,
YLZRS,
YLZJE,
LLZRS,
GLLR,xh)
select '合计',sum(zrs),sum(CZJM),
sum(LDRK),
sum(DBZRS),
sum(DBZHS),
sum(DSRZRS),
sum(DSRZHS),
sum(WQGZRS),
sum(WQGZJE),
sum(YLZRS),
sum(YLZJE),
sum(LLZRS),
sum(GLLR),'999' from SWSQ_ZRS_HJ_ZFJZ_YL_LL ;
open c for
select JDMC 街,
ZRS 总人数,
CZJM 常住居民,
LDRK 流动人口,
DBZRS 低保户总人数,
DBZHS 低保户总户数,
DSRZRS 低收入总人数,
DSRZHS 低收入总户数,
WQGZRS 五七工总人数,
WQGZJE 五七工总金额,
YLZRS 医疗救助总人数,
YLZJE 医疗救助总金额,
LLZRS 老龄事物总人数,
GLLR 高龄老人
from SWSQ_ZRS_HJ_ZFJZ_YL_LL
order by zrs;
/*end if;*/