view_loc.sql

公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统

drop  VIEW vw_yqxx;
CREATE VIEW vw_yqxx
( xh0000,zzxxzj,jsrq00,yxq000,sfdj00,sfyy00,
  zcly00,zcrq00,back01,back02,scry00,scrq00,
  zlbz00,bz0000,xm0000,sfzhm0,zzzbh0,zcdpcs)
AS select
  yqb000.xh0000,yqb000.zzxxzj,yqb000.jsrq00,
  yqb000.yxq000,yqb000.sfdj00,yqb000.sfyy00,
  yqb000.zcly00,yqb000.zcrq00,yqb000.back01,
  yqb000.back02,yqb000.scry00,yqb000.scrq00,
  yqb000.zlbz00,yqb000.bz0000,zzxxjl.xm0000,
  zzxxjl.sfzhm0,zzxxjl.zzzbh0,zzxxjl.zcdpcs
  from zk.yqb000,
  zk.zzxxjl where(zzxxjl.xh0000=yqb000.zzxxzj);
  

drop view vw_zxxx;  
CREATE VIEW vw_zxxx    
    ( xh0000,zxyy00,zxrq00,qxss00,qzxz00,   
      zcly00,zcrq00,zlbz00,xm0000,zzzbh0 ) AS   
  SELECT zzxxzx.xh0000,zzxxzx.zxyy00,zzxxzx.zxrq00,
         zzxxzx.qxss00,zzxxzx.qzxz00,zzxxzx.zcly00,
         zzxxzx.zcrq00,zzxxzx.zlbz00,zzxxjl.xm0000,
         zzxxjl.zzzbh0 FROM zzxxjl,zzxxzx  
   WHERE ( zzxxjl.xh0000 = zzxxzx.xh0000 )    
   

--把本地派出所的信息插入临时表
--把要统计的时间写入派出所
--计算所有未注销和未删除的人员
--临时表的DATA06用于区分统计单位：1，派出所。2分局。3市局(在此至空)
--临时表的data01,data02,data03:至空
--临时表的data04,data05 为日期间隔

==========================计算所有暂住证超期人员清单==========================

--临时表的data01,data02,data03:行政区划，乡镇，派出所 (2=1234567)
--临时表的data04 为截止日期20001014

drop VIEW VW_ZZXXJL_gqryqd;

CREATE VIEW VW_ZZXXJL_gqryqd
    ( xh0000,fzxm00,fzsfzh,xm0000,sfzhm0,zzzbh0,
      lbdrq0,xcszy0,xffcs0,zcdssx,zcdxz0,zcdpcs,zcdjwh,
      zcdljx,zcdmph,zcdfh0,zcdfjh,qfrq00,yxqx00,yqrq00,
      yqqx00,tbr000) AS   
  SELECT z.xh0000,z.fzxm00,z.fzsfzh,z.xm0000,z.sfzhm0,
	 z.zzzbh0,z.lbdrq0,z.xcszy0,z.xffcs0,z.zcdssx,z.zcdxz0,
	 z.zcdpcs,z.zcdjwh,z.zcdljx,z.zcdmph,z.zcdfh0,z.zcdfjh,
	 z.qfrq00,z.yxqx00,z.yqrq00,z.yqqx00,z.tbr000
    FROM zzxxjl z,zktemp t  
   WHERE dayaftermonths (yqrq00,yqqx00,qfrq00,yxqx00) < date(t.data04) and z.zlbz00 = 'I';


==========================计算所有暂住证有效人员清单==========================
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--临时表的data04 为截止日期20001014

drop view VW_ZZXXJL_yxryqd  ;

CREATE  VIEW VW_ZZXXJL_yxryqd  
    ( xh0000,fzxm00,fzsfzh,xm0000,sfzhm0,zzzbh0,
      lbdrq0,xcszy0,xffcs0,zcdssx,zcdxz0,zcdpcs,zcdjwh,
      zcdljx,zcdmph,zcdfh0,zcdfjh,qfrq00,yxqx00,yqrq00,
      yqqx00,tbr000) AS   
  SELECT z.xh0000,z.fzxm00,z.fzsfzh,z.xm0000,z.sfzhm0,
	 z.zzzbh0,z.lbdrq0,z.xcszy0,z.xffcs0,z.zcdssx,z.zcdxz0,
	 z.zcdpcs,z.zcdjwh,z.zcdljx,z.zcdmph,z.zcdfh0,z.zcdfjh,
	 z.qfrq00,z.yxqx00,z.yqrq00,z.yqqx00,z.tbr000
    FROM zzxxjl z,zktemp t  
   WHERE dayaftermonths (yqrq00,yqqx00,qfrq00,yxqx00) >= date(t.data04) and z.zlbz00 = 'I';

========================计算工作清单(全部清单)==========================
drop view vw_gzqd;

CREATE VIEW vw_gzqd  
    ( czry00,czrq00,zjbh00,xm0000,sfbz00,dx,cz,xzqh00,xzjd00,pcs000 ) AS   
  SELECT z.czry00,z.czrq00,z.zzzbh0,z.xm0000,z.ysfy00,'暂住证','新办',z.zcdssx,z.zcdxz0,z.zcdpcs
  FROM zzxxjl z,zktemp t
  WHERE  z.czrq00 >= t.data04 and z.czrq00 <= t.data05  
  union all
  SELECT z.scry00,z.scrq00,z.zzzbh0,z.xm0000,z.ysfy00,'暂住证','撤消',z.zcdssx,z.zcdxz0,z.zcdpcs  
  FROM  zzxxjl z,zktemp t
  where  z.scrq00 >= t.data04 and z.scrq00 <= t.data05 and z.zlbz00 = 'D' 
  union all
  SELECT x.zcly00,x.zcrq00,z.zzzbh0,z.xm0000,'','暂住证','注销',z.zcdssx,z.zcdxz0,z.zcdpcs  
  FROM  zzxxjl z,zktemp t,zzxxzx x
  where  x.xh0000 = z.xh0000 and x.zcrq00 >= t.data04 and 
         x.zcrq00 <= t.data05 and z.zlbz00 = 'Z'          
  union all
  SELECT y.zcly00,y.zcrq00,z.zzzbh0,z.xm0000,y.sfyy00,'延期','新办',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM yqb000 Y,zzxxjl Z,zktemp t 
   WHERE y.zzxxzj = z.xh0000 and y.zcrq00 >= t.data04 and y.zcrq00 <= t.data05    
  union all
  SELECT y.scry00,y.scrq00,z.zzzbh0,z.xm0000,y.sfyy00,'延期','撤消',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM yqb000 Y,zzxxjl Z,zktemp t 
   WHERE y.zzxxzj = z.xh0000 and y.scrq00 >= t.data04 and
         y.scrq00 <= t.data05 and y.zlbz00 = 'D'    
   union all
   SELECT Q.czry00,Q.czrq00,Z.zzzbh0,Z.xm0000,'','迁移','新办',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM qyb000 Q,zzxxjl Z,zktemp T 
   WHERE Q.zzxxzj = Z.xh0000 and Q.czrq00 >= t.data04 and Q.czrq00 <= t.data05
   union all
   SELECT Q.scry00,Q.scrq00,Z.zzzbh0,Z.xm0000,'','迁移','撤消',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM qyb000 Q,zzxxjl Z,zktemp T 
   WHERE Q.zzxxzj = Z.xh0000 and Q.scrq00 >= t.data04 and 
         Q.scrq00 <= t.data05 and Q.zlbz00 = 'D'
   union all
   SELECT F.czrq00,F.czry00,F.zzzbh0,F.XM0000,'','犯罪信息','新办',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM fzdjb0 F,zzxxjl Z,zktemp T    
   WHERE F.zzzbh0 = Z.zzzbh0 and F.czrq00 >= t.data04 and F.czrq00 <= t.data05
   union all
  SELECT F.scry00,F.scrq00,F.zzzbh0,'','','犯罪信息','撤消',z.zcdssx,z.zcdxz0,z.zcdpcs
    FROM fzdjb0 F,zzxxjl Z,zktemp T 
   WHERE F.zzzbh0 = Z.zzzbh0 and F.scrq00 >= t.data04 and
         F.scrq00 <= t.data05 and F.zlbz00 = 'D'  
   union all
  SELECT x.zcly00,x.zcrq00,x.czfbh0,'',x.ysfy00,'许可证','新办',x.fzss00,x.fzxz00,x.fzpcs0
    FROM xkz000 X,zktemp t
   WHERE x.zcrq00 >= t.data04 and x.zcrq00 <= t.data05
   union all
  SELECT x.scry00,x.scrq00,x.czfbh0,'',x.ysfy00,'许可证','撤消',x.fzss00,x.fzxz00,x.fzpcs0
    FROM xkz000 X,zktemp t
   WHERE x.scrq00 >= t.data04 and x.scrq00 <= t.data05 and x.zlbz00 = 'D'
   union all
  SELECT x.nsry00,X.nsrq00,x.czfbh0,'',x.ysfy00,'许可证','年审',x.fzss00,x.fzxz00,x.fzpcs0
    FROM xkz000 X,zktemp t
   WHERE x.nsrq00 >= t.data04 and x.nsrq00 <= t.data05 and x.ns0000 = '1'  ;



==================产生房东统计表(不含实际居住人数)===================
--------------含房东信息，许可证信息，出租房信息--------------

drop VIEW VW_FD_CZF_TJQD1;
CREATE  VIEW VW_FD_CZF_TJQD1
    ( FDXM00,FDSFZH,SEX000,FDXZ00,ZFXZ00,FDSZDQ,FDSZDX,FDPCS0,FDLM00,
      FDMP00,FDDW00,FDDH00,CZFBH0,ZZRS00,FZRQ00,NSRY00,CZFLM0,CZFMPH,
      FH0000,FJH000,XH0000 ) AS   
  SELECT H.FDXM00,H.FDSFZH,H.SEX000,H.FDXZ00,C.ZFXZ00,H.FDSZDQ,
         H.FDSZDX,H.FDPCS0,H.FDLM00,H.FDMP00,H.FDDW00,H.FDDH00,
         X.CZFBH0,X.ZZRS00,X.FZRQ00,X.NSRY00,C.CZFLM0,C.CZFMPH,
         C.FH0000,C.FJH000,H.XH0000 
from(HOUSER H left outer join czfxxb C on H.xh0000=C.fdzj00),
    (houser H left outer join xkz000 X on H.xh0000=X.fdzj00)
where H.zlbz00='I';

---------------------实际居住人数---------------------------------------

DROP VIEW VW_FD_CZF_TJQD2;

CREATE VIEW VW_FD_CZF_TJQD2
    ( fdzj00, sjzzrs) AS   
  SELECT c.fdzj00,count(z.xh0000)
    FROM czfxxb c,zzxxjl z 
    where c.czfmph = z.zcdmph and z.zcdljx = c.czflm0 and z.zlbz00 = 'I'
GROUP BY c.fdzj00,z.zcdmph;

--------------------------房东及出租房统计----------------

DROP VIEW VW_FD_CZF_TJQD;

CREATE VIEW VW_FD_CZF_TJQD
    ( FDXM00,FDSFZH,SEX000,FDXZ00,ZFXZ00,FDSZDQ,FDSZDX,FDPCS0,FDLM00,
      FDMP00,FDDW00,FDDH00,CZFBH0,ZZRS00,SJZZRS,FZRQ00,NSRY00,CZFLM0,
      CZFMPH,FH0000,FJH000 ) AS SELECT 
      V1.FDXM00,V1.FDSFZH,V1.SEX000,V1.FDXZ00,V1.ZFXZ00,V1.FDSZDQ,V1.FDSZDX,V1.FDPCS0,V1.FDLM00,
      V1.FDMP00,V1.FDDW00,V1.FDDH00,V1.CZFBH0,V1.ZZRS00,V2.SJZZRS,V1.FZRQ00,V1.NSRY00,V1.CZFLM0,
      V1.CZFMPH,V1.FH0000,V1.FJH000 
      FROM (VW_FD_CZF_TJQD1 V1 left outer join VW_FD_CZF_TJQD2 V2 on V1.XH0000=V2.FDZJ00);


===================以下视图产生暂住人口统计表============================
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--临时表的data04 为统计日期 + 1 如统计划200010则为20001031

------------------sex-------------------------
DROP VIEW vw_zzrktjb_xb;
CREATE VIEW vw_zzrktjb_xb  
    ( zcsy00,   
      SM0000,   
      rs ) AS   
  SELECT Z.zcsy00,   
         'sex00-'||Z.XB0000,   
         count(distinct Z.RYBH00)  
    FROM ZZXXJL Z,ZKTEMP T 
   WHERE Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04 
GROUP BY Z.zcsy00,Z.XB0000 ;
          
-------------------time-----------------------          
DROP VIEW VW_ZZRKTJB_SJ1;
CREATE  VIEW VW_ZZRKTJB_SJ1  
    ( month,   
      RYBH00,   
      zcsy00 ) AS   
  SELECT fun_months(Z.lbdrq0),   
         Z.RYBH00,   
         Z.zcsy00  
    FROM ZZXXJL Z,ZKTEMP T 
     where Z.zlbz00 = 'I'  AND Z.TBRQ00 <= T.DATA04;         
  
DROP VIEW VW_ZZRKTJB_SJ;
CREATE VIEW VW_ZZRKTJB_SJ  
    ( zcsy00,   
      SM0000,   
      RS ) AS   
  SELECT zcsy00,   
         'time0-'||MONTH,   
         COUNT(distinct RYBH00 )  
    FROM VW_ZZRKTJB_SJ1  
GROUP BY zcsy00,MONTH ;

-------------------from-----------------------
DROP VIEW VW_ZZRKTJB_from1;
CREATE VIEW VW_ZZRKTJB_from1  
    ( FROM00,   
      RYBH00,   
      zcsy00 ) AS   
  SELECT fun_from(Z.czhkss),   
         Z.RYBH00,   
         Z.zcsy00  
    FROM ZZXXJL Z,ZKTEMP T 
 where Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04 ;    

DROP VIEW  VW_ZZRKTJB_FROM;
CREATE VIEW VW_ZZRKTJB_FROM
    ( zcsy00,   
      SM0000,   
      RS ) AS   
  SELECT zcsy00,   
         'from0-'||FROM00,   
         COUNT(distinct RYBH00)  
    FROM VW_ZZRKTJB_FROM1  
GROUP BY zcsy00,FROM00  ;

------------------------PLACE------------------
DROP VIEW VW_ZZRKTJB_PLACE;
CREATE VIEW VW_ZZRKTJB_PLACE
    ( zcsy00,   
      SM0000,   
      RS ) AS   
  SELECT Z.zcsy00,   
         'place-'||SUBSTR(Z.ZCCS00,2),   
         COUNT(distinct Z.RYBH00)  
    FROM ZZXXJL Z,ZKTEMP T 
  where  Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04  
GROUP BY Z.zcsy00,Z.ZCCS00 ;

------------------------ALL--------------------
DROP VIEW VW_ZZRKTJB1;
CREATE  VIEW VW_ZZRKTJB1
    (zcsy00,SM0000,RS) AS
  select * from vw_zzrktjb_xb union all
  select * from VW_ZZRKTJB_SJ  union all
  select * from VW_ZZRKTJB_FROM union all
  select * from VW_ZZRKTJB_PLACE ;
    
-----------------------------------------
-----------------------------------------------
DROP VIEW VW_ZZRKTJB2;
CREATE VIEW VW_ZZRKTJB2 AS   
  SELECT zcsy00                              zcsy00,
         MAX(fun_match(SM0000,'sex00-1',RS)) sex1,   
         MAX(fun_match(SM0000,'sex00-2',RS)) sex2, 
         MAX(fun_match(SM0000,'time0-1',RS)) time1, 
         MAX(fun_match(SM0000,'time0-2',RS)) time2,
         MAX(fun_match(SM0000,'time0-3',RS)) time3, 
         MAX(fun_match(SM0000,'from0-1',RS)) from1, 
         MAX(fun_match(SM0000,'from0-2',RS)) from2, 
         MAX(fun_match(SM0000,'from0-3',RS)) from3,
         MAX(fun_match(SM0000,'from0-4',RS)) from4,
         MAX(fun_match(SM0000,'from0-5',RS)) from5, 
         MAX(fun_match(SM0000,'from0-6',RS)) from6, 
         MAX(fun_match(SM0000,'place-1',RS)) place1, 
         MAX(fun_match(SM0000,'place-2',RS)) place2, 
         MAX(fun_match(SM0000,'place-3',RS)) place3, 
         MAX(fun_match(SM0000,'place-4',RS)) place4, 
         MAX(fun_match(SM0000,'place-5',RS)) place5, 
         MAX(fun_match(SM0000,'place-6',RS)) place6 
    FROM VW_ZZRKTJB1  
GROUP BY zcsy00 ;
--------------------------------------------------------
DROP VIEW vw_zzrktjb3;
create view vw_zzrktjb3 
       (code, mesg) as
       select '01','务        工' 
union  select '02','务        农' 
union  select '03','经        商' 
union  select '04','服        务' 
union  select '05','因公出差'     
union  select '06','借读培训'     
union  select '07','治病疗养'     
union  select '08','保        姆' 
union  select '09','投亲靠友'     
union  select '10','探亲访友'     
union  select '11','旅游观光'     
union  select '12','其        它' ;

--------------------------------------------------------
DROP VIEW VW_ZZRKTJB;
CREATE VIEW VW_ZZRKTJB AS   
  SELECT  V3.MESG     MESG,
          V3.CODE     CODE,
          V2.sex1     sex1,   
          V2.sex2     sex2,  
          V2.time1    time1, 
          V2.time2    time2, 
          V2.time3    time3, 
          V2.from1    from1, 
          V2.from2    from2, 
          V2.from3    from3, 
          V2.from4    from4, 
          V2.from5    from5, 
          V2.from6    from6, 
          V2.place1   place1, 
          V2.place2   place2, 
          V2.place3   place3, 
          V2.place4   place4, 
          V2.place5   place5, 
          V2.place6   place6 
    FROM (vw_zzrktjb3 v3 left outer join vw_zzrktjb2 v2 on v3.code=v2.zcsy00);

=========================暂住人口统计表结束=========================

=========================统计出新办暂住证的清单======================
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--办证统计-------DATA04为统计时间200010表示为200010%

drop view vw_bztj00;
CREATE VIEW vw_bztj00
    ( xh0000,xm0000,yfzgx0,fzxm00,
      czhkss,czhkxz,lbdrq0,zcdssx,
      zcdxz0,zcdpcs,zcdjwh,zcdljx,
      zcdmph,yxqx00,yqrq00,tbr000 ) AS   
  SELECT z.xh0000,z.xm0000,z.yfzgx0,z.fzxm00,
         z.czhkss,z.czhkxz,z.lbdrq0,z.zcdssx,
         z.zcdxz0,z.zcdpcs,z.zcdjwh,z.zcdljx,
         z.zcdmph,z.yxqx00,z.yqrq00,z.tbr000  
    FROM zzxxjl Z,zktemp T 
   WHERE  z.qfrq00 like rtrim(t.data04);


=============================延期统计===============================
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--办证统计-------DATA04为统计时间200010表示为200010%

drop view vw_yqtj00;
CREATE  VIEW vw_yqtj00
    ( xh0000,xm0000,yfzgx0,fzxm00,
      czhkss,czhkxz,lbdrq0,zcdssx,
      zcdxz0,zcdpcs,zcdjwh,zcdljx,
      zcdmph,yxqx00,yqrq00,tbr000 ) AS   
  SELECT z.xh0000,z.xm0000,z.yfzgx0,z.fzxm00,
         z.czhkss,z.czhkxz,z.lbdrq0,z.zcdssx,
         z.zcdxz0,z.zcdpcs,z.zcdjwh,z.zcdljx,
         z.zcdmph,z.yxqx00,z.yqrq00,z.tbr000 
    FROM zzxxjl Z,zktemp T 
   WHERE z.yqrq00 like rtrim(t.data04);