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;