📄 view_ora.sql.bak
字号:
--把本地派出所的信息插入临时表
--把要统计的时间写入派出所
--计算所有未注销和未删除的人员
--临时表的DATA06用于区分统计单位：1，派出所。2分局。3市局
--临时表的data01,data02,data03:行政区划，乡镇，派出所
--临时表的data04,data05 为日期间隔
---------------计算所有暂住证超期人员清单-------------------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所 (2=1234567)
--临时表的data04 为截止日期20001014
CREATE OR REPLACE 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 rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
decode(add_months(to_date(z.yqrq00,'yyyymmdd'),z.yqqx00),
'',add_months(to_date(z.qfrq00,'yyyymmdd'),z.yxqx00),
add_months(to_date(z.yqrq00,'yyyymmdd'),z.yqqx00))
< to_date(t.data04,'yyyymmdd') and z.zlbz00 = 'I'
/
--------------计算所有暂住证有效人员清单-------------------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--临时表的data04 为截止日期20001014
CREATE OR REPLACE 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 rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
decode(add_months(to_date(z.yqrq00,'yyyymmdd'),z.yqqx00),
'',add_months(to_date(z.qfrq00,'yyyymmdd'),z.yxqx00),
add_months(to_date(z.yqrq00,'yyyymmdd'),z.yqqx00))
>= to_date(t.data04,'yyyymmdd') and z.zlbz00 = 'I'
/
----------------计算工作清单(全部清单)----------------------------
CREATE OR REPLACE VIEW vw_gzqd1
( 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,'','','犯罪信息','新办',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'
/
-------------------------工作清单(按条件过滤)-----------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--临时表的data04,DATA05 为起始日期和截止日期20000101,20001014
CREATE OR REPLACE VIEW vw_gzqd
( czry00,czrq00,zjbh00,xm0000,sfbz00,dx,cz,xzqh00,xzjd00,pcs000 ) as select
czry00,czrq00,zjbh00,xm0000,sfbz00,dx,cz,xzqh00,xzjd00,pcs000 from vw_gzqd1 v,zktemp t
WHERE rtrim(decode(t.data06,'1',v.xzqh00,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',v.xzjd00,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',v.pcs000,'2',substr(v.pcs000,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1'))
/
---------------产生房东统计表(不含实际居住人数)----------------
--------------含房东信息，许可证信息，出租房信息--------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
CREATE OR REPLACE 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 CZFXXB C,HOUSER H,XKZ000 X ,zktemp t
WHERE rtrim(decode(t.data06,'1',C.czfszq,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',C.czfszx,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',C.czfpcs,'2',substr(C.czfpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
( C.FDZJ00(+) = H.XH0000 ) and ( X.FDZJ00(+) = H.XH0000 ) and (x.zlbz00 = 'I')
/
---------------------实际居住人数---------------------------------------
CREATE OR REPLACE 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
/
--------------------------房东及出租房统计----------------
CREATE OR REPLACE 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,VW_FD_CZF_TJQD2 V2
WHERE V1.XH0000 = V2.FDZJ00(+)
/
------------------------------------------------------------------------------
--------以下视图产生暂住人口统计表--------------------------------------------
--临时表的data01,data02,data03:行政区划，乡镇，派出所(2=1234567)
--临时表的data04 为统计日期 + 1 如统计划200010则为20001031
------------------sex-------------------------
CREATE or replace VIEW vw_zzrktjb_xb
( zcsy00,
SM0000,
rs ) AS
SELECT Z.zcsy00,
'sex00-'||Z.XB0000,
count(distinct(Z.RYBH00))
FROM ZZXXJL Z,ZKTEMP T
WHERE rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04
GROUP BY Z.zcsy00,Z.XB0000
/
-------------------time-----------------------
CREATE or replace VIEW VW_ZZRKTJB_SJ1
( month,
RYBH00,
zcsy00 ) AS
SELECT TO_CHAR(fun_months(Z.lbdrq0)),
Z.RYBH00,
Z.zcsy00
FROM ZZXXJL Z,ZKTEMP T
where rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04
/
CREATE OR REPLACE VIEW VW_ZZRKTJB_SJ
( zcsy00,
SM0000,
RS ) AS
SELECT zcsy00,
'time0-'||MONTH,
COUNT(distinct(RYBH00))
FROM VW_ZZRKTJB_SJ1
GROUP BY zcsy00,MONTH
/
-------------------from-----------------------
CREATE or replace VIEW VW_ZZRKTJB_from1
( FROM00,
RYBH00,
zcsy00 ) AS
SELECT fun_from(Z.czhkss),
Z.RYBH00,
Z.zcsy00
FROM ZZXXJL Z,ZKTEMP T
where rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04
/
CREATE OR REPLACE VIEW VW_ZZRKTJB_FROM
( zcsy00,
SM0000,
RS ) AS
SELECT zcsy00,
'from0-'||FROM00,
COUNT(distinct(RYBH00))
FROM VW_ZZRKTJB_FROM1
GROUP BY zcsy00,FROM00
/
------------------------PLACE------------------
CREATE OR REPLACE 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 rtrim(decode(t.data06,'1',z.zcdssx,'1')) = rtrim(decode(t.data06,'1',t.data01,'1')) and
rtrim(decode(t.data06,'1',z.zcdxz0,'1')) = rtrim(decode(t.data06,'1',t.data02,'1')) and
rtrim(decode(t.data06,'1',z.zcdpcs,'2',substr(z.zcdpcs,1,7),'1')) =
rtrim(decode(t.data06,'1',t.data03,'2',t.data03,'1')) and
Z.zlbz00 = 'I' AND Z.TBRQ00 <= T.DATA04
GROUP BY Z.zcsy00,Z.ZCCS00
/
------------------------ALL--------------------
CREATE OR REPLACE 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
/
-----------------------------------------
-----------------------------------------------
CREATE OR REPLACE VIEW VW_ZZRKTJB2 AS
SELECT zcsy00 zcsy00,
MAX(DECODE(SM0000,'sex00-1',RS,'')) sex1,
MAX(DECODE(SM0000,'sex00-2',RS,'')) sex2,
MAX(DECODE(SM0000,'time0-1',RS,'')) time1,
MAX(DECODE(SM0000,'time0-2',RS,'')) time2,
MAX(DECODE(SM0000,'time0-3',RS,'')) time3,
MAX(DECODE(SM0000,'from0-1',RS,'')) from1,
MAX(DECODE(SM0000,'from0-2',RS,'')) from2,
MAX(DECODE(SM0000,'from0-3',RS,'')) from3,
MAX(DECODE(SM0000,'from0-4',RS,'')) from4,
MAX(DECODE(SM0000,'from0-5',RS,'')) from5,
MAX(DECODE(SM0000,'from0-6',RS,'')) from6,
MAX(DECODE(SM0000,'place-1',RS,'')) place1,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -