oracle迁移物化视图,oracle 物化视图

CREATE MATERIALIZED VIEW MV_PVBDP_STATISTICS_ALERT

REFRESH FORCE ON DEMAND

START WITH TO_DATE('18-01-2017 17:00:00', 'DD-MM-YYYY HH24:MI:SS') NEXT TRUNC(SYSDATE,'HH24')+1/24

AS

SELECT B.XZQH, B.ZZJGDM, C.TJRQ, C.YJLX, C.CLZT, COUNT(1) YJSL

FROM (SELECT T3.YJBH, t3.zdrybh

FROM T_PVBDP_ALERT_RELATED T3,

(SELECT T1.ZDRYBH, T2.BKBH

FROM T_PVBDP_PERSON_COLLECTION T1,

T_PVBDP_PERSON_DISPATCHED T2

WHERE T1.ZDRYBH = T2.ZDRYBH

AND T1.SCBS = '0'

and t2.YXX = '1'

AND T1.SFZRR = '1'

and t2.BKZTDM = '1') T4

WHERE T3.BKBH = T4.BKBH

AND T3.ZDRYBH = T4.ZDRYBH

GROUP BY T3.YJBH, t3.zdrybh, t3.yjlx) a,

(select ZRRDWDM zzjgdm, ZRRXZQHID xzqh, zdrybh

from t_pvbdp_person_collection) b,

(

--获取从20170101到当前时间的预警信息

select yjlx, clzt, yjbh, t2.daylist tjrq

from t_pvbdp_alert t1,

(SELECT TO_CHAR(TO_DATE('2017-01-01', 'yyyy-MM-dd') +

ROWNUM - 1,

'yyyyMMdd') as daylist

FROM DUAL

CONNECT BY ROWNUM <=

trunc(to_date(to_char(sysdate,

'yyyy-MM-dd'),

'yyyy-MM-dd') -

to_date('2017-01-01', 'yyyy-MM-dd')) + 1) t2

where substr(t1.YJSJ, 1, 8) = t2.daylist) c

where a.zdrybh = b.zdrybh

and a.yjbh = c.yjbh

group by b.xzqh, b.zzjgdm, c.yjlx, c.clzt, c.tjrq;