加入收藏 | 设为首页 | 会员中心 | 我要投稿 鄂州站长网 (https://www.0711zz.com/)- 数据分析、网络、云渲染、应用安全、大数据!
当前位置: 首页 > 数据库 > Oracle > 正文

oracle 物化视图

发布时间:2020-07-21 08:44:43 所属栏目:Oracle 来源:互联网
导读:CREATE MATERIALIZED VIEW MV_PVBDP_STATISTICS_ALERTREFRESH FORCE ON DEMANDSTART WITH TO_DATE(18-01-2017 17:00:00, DD-MM-YYYY HH24:MI:SS) NEXT TRUNC(SYSDATE,HH24)+1/24 ASSELECT B.XZQH, B.ZZJGD


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;

(编辑:鄂州站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读