水利防汛之水庫和河道預警SQL語句

SQL Server版本：

create view v_HD_WarningInfoasSELECT     t .zdt8 , t .tmnow , t .STCD , t .STNM, t .STLC, t .LGTD, t .LTTD, t .ShowLevel, t .WRZ, t .GRZ, tt.STTP, tt.RVNM,                       t .MsgFROM         (SELECT     t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,                                               CASE WHEN WRZ - zdt8 = 0 THEN '達警戒' WHEN zdt8 BETWEEN WRZ AND GRZ THEN '超警戒:' + CAST((zdt8 - WRZ) AS varchar(10))                                               WHEN zdt8 > GRZ THEN '超保證:' + CAST((zdt8 - GRZ) AS varchar(10)) ELSE '正常' END AS Msg                       FROM          (SELECT     STCD, STNM, LGTD, LTTD, STLC, Z AS zdt8, tmnow, ShowLevel                                               FROM          ST_RIVER_D) t1 LEFT JOIN                                                  (SELECT     STCD, ISNULL(WRZ, 0) AS WRZ, ISNULL(GRZ, 0) AS GRZ                                                    FROM          ST_RVFCCH_B) t2 ON t1.STCD = t2.STCD                       UNION ALL                       SELECT     t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,                                              CASE WHEN WRZ - zdt8 = 0 THEN '達汛限' WHEN zdt8 > WRZ THEN '超汛限:' + CAST((zdt8 - WRZ) AS varchar(10)) ELSE '正常' END AS Msg                       FROM         (SELECT     STCD, STNM, LGTD, LTTD, STLC, RZ AS zdt8, tmnow, ShowLevel                                              FROM          ST_RSVR_D) t1 LEFT JOIN                                                 (SELECT     STCD, BGMD, EDMD, ISNULL(FSLTDZ, 0) AS WRZ, 0 AS GRZ                                                   FROM          ST_RSVRFSR_B) t2 ON t1.STCD = t2.STCD AND t1.tmnow BETWEEN CONVERT(varchar, YEAR(GETDATE()), 4) + '-' + SUBSTRING(t2.BGMD, 1,                                              2) + '-' + SUBSTRING(t2.BGMD, 3, 2) AND CONVERT(varchar, YEAR(GETDATE()), 4) + '-' + SUBSTRING(t2.EDMD, 1, 2) + '-' + SUBSTRING(t2.EDMD, 3, 2))                       AS t INNER JOIN                          (SELECT     STCD, STTP, RVNM                            FROM          dbo.ST_STBPRP_B                            WHERE      (STTP = 'RR')) AS tt ON t .STCD = tt.STCDWHERE     (t .Msg <> '正常') AND t .tmnow >= dateadd([day], - 3, GETDATE());-------------------------------------------------------------------------------------------------create view v_SK_WarningInfoasSELECT     t .zdt8, t .tmnow , t .STCD , t .STNM , t .STLC , t .LGTD, t .LTTD, t .ShowLevel, t .WRZ, t .GRZ, tt.STTP, tt.RVNM,                       t .MsgFROM         (SELECT     t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,                                               CASE WHEN WRZ - zdt8 = 0 THEN '達警戒' WHEN zdt8 BETWEEN WRZ AND GRZ THEN '超警戒:' + CAST((zdt8 - WRZ) AS varchar(10))                                               WHEN zdt8 > GRZ THEN '超保證:' + CAST((zdt8 - GRZ) AS varchar(10)) ELSE '正常' END AS Msg                       FROM          (SELECT     STCD, STNM, LGTD, LTTD, STLC, Z AS zdt8, tmnow, ShowLevel                                               FROM          ST_RIVER_D) t1 LEFT JOIN                                                  (SELECT     STCD, ISNULL(WRZ, 0) AS WRZ, ISNULL(GRZ, 0) AS GRZ                                                    FROM          ST_RVFCCH_B) t2 ON t1.STCD = t2.STCD                       UNION ALL                       SELECT     t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,                                              CASE WHEN WRZ - zdt8 = 0 THEN '達汛限' WHEN zdt8 > WRZ THEN '超汛限:' + CAST((zdt8 - WRZ) AS varchar(10)) ELSE '正常' END AS Msg                       FROM         (SELECT     STCD, STNM, LGTD, LTTD, STLC, RZ AS zdt8, tmnow, ShowLevel                                              FROM          ST_RSVR_D) t1 LEFT JOIN                                                 (SELECT     STCD, BGMD, EDMD, ISNULL(FSLTDZ, 0) AS WRZ, 0 AS GRZ                                                   FROM          ST_RSVRFSR_B) t2 ON t1.STCD = t2.STCD AND t1.tmnow BETWEEN CONVERT(varchar, YEAR(GETDATE()), 4) + '-' + SUBSTRING(t2.BGMD, 1,                                              2) + '-' + SUBSTRING(t2.BGMD, 3, 2) AND CONVERT(varchar, YEAR(GETDATE()), 4) + '-' + SUBSTRING(t2.EDMD, 1, 2) + '-' + SUBSTRING(t2.EDMD, 3, 2))                       AS t INNER JOIN                          (SELECT     STCD, STTP, RVNM                            FROM          dbo.ST_STBPRP_B                            WHERE      (STTP = 'ZZ')) AS tt ON t .STCD = tt.STCDWHERE     (t .Msg <> '正常') AND t .tmnow >= dateadd([day], - 3, GETDATE());

Oracle版本：

--水庫預警create view v_SK_WarningInfoasSELECT       t.zdt8 AS s, t.tmnow, t.STCD , t.STNM ,   t.STLC, t.LGTD, t.LTTD, t.ShowLevel, t.WRZ, t.GRZ, tt.STTP, tt.RVNM,t.MsgFROM (      SELECT           t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,           CASE WHEN WRZ - zdt8 = 0 THEN '達警戒' WHEN zdt8 BETWEEN WRZ AND GRZ THEN '超警戒:' || CAST((NVL(zdt8,0) - NVL(WRZ,0)) AS varchar2(10))           WHEN zdt8 > GRZ THEN '超保證:' || CAST((NVL(zdt8,0) - NVL(GRZ,0)) AS varchar2(10)) ELSE '正常' END AS Msg      FROM                (          SELECT                   STCD, STNM, LGTD, LTTD, STLC, Z AS zdt8, tmnow, ShowLevel          FROM                   ST_RIVER_D       ) t1      LEFT JOIN      (         SELECT                  STCD, NVL(WRZ, 0) AS WRZ, NVL(GRZ, 0) AS GRZ         FROM          ST_RVFCCH_B       ) t2       ON t1.STCD = t2.STCD                    UNION ALL          SELECT           t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,           CASE WHEN WRZ - zdt8 = 0 THEN '達汛限' WHEN zdt8 > WRZ THEN '超汛限:' || CAST((zdt8 - WRZ) AS varchar2(10)) ELSE '正常' END AS Msg       FROM       (            SELECT               STCD, STNM, LGTD, LTTD, STLC, RZ AS zdt8, tmnow, ShowLevel           FROM            ST_RSVR_D         ) t1       LEFT JOIN       (           SELECT               STCD, BGMD, EDMD, NVL(FSLTDZ, 0) AS WRZ, 0 AS GRZ           FROM             ST_RSVRFSR_B       ) t2        ON t1.STCD = t2.STCD AND           t1.tmnow BETWEEN (select to_char(sysdate,'yyyy') from dual) || '/' || substr(t2.BGMD, 1,           2) || '/' || substr(t2.BGMD, 3, 2) AND (select to_char(sysdate,'yyyy') from dual) || '/' || substr(t2.EDMD, 1, 2) || '/' || substr(t2.EDMD, 3, 2))  t INNER JOIN(   SELECT STCD, STTP, RVNM FROM ST_STBPRP_B   WHERE STTP = 'RR')  ttON t .STCD = tt.STCDWHERE t.Msg != '正常' AND t.tmnow >= (select sysdate-1 from dual);--河道預警create view v_HD_WarningInfoasSELECT     t.zdt8 AS s, t.tmnow, t.STCD, t.STNM, t.STLC, t.LGTD,     t.LTTD, t.ShowLevel, t.WRZ, t.GRZ, tt.STTP, tt.RVNM, t .Msg    FROM     (         SELECT  t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,             CASE WHEN WRZ - zdt8 = 0 THEN '達警戒' WHEN zdt8 BETWEEN WRZ AND GRZ THEN '超警戒:' || CAST((NVL(zdt8,0) - NVL(WRZ,0)) AS varchar2(10))             WHEN zdt8 > GRZ THEN '超保證:' || CAST((NVL(zdt8,0) - NVL(GRZ,0)) AS varchar2(10)) ELSE '正常' END AS Msg         FROM         (                 SELECT STCD, STNM, LGTD, LTTD, STLC, Z AS zdt8, tmnow, ShowLevel                      FROM  ST_RIVER_D         ) t1         LEFT JOIN         (             SELECT STCD, NVL(WRZ, 0) AS WRZ, NVL(GRZ, 0) AS GRZ                    FROM  ST_RVFCCH_B         ) t2 ON t1.STCD = t2.STCD                 UNION ALL                SELECT  t1.STCD, t1.STNM, t1.LGTD, t1.LTTD, t1.STLC, t1.zdt8, t1.tmnow, t1.ShowLevel, t2.WRZ, t2.GRZ,                 CASE WHEN WRZ - zdt8 = 0 THEN '達汛限' WHEN zdt8 > WRZ THEN '超汛限:' || CAST((zdt8 - WRZ) AS varchar(10)) ELSE '正常' END AS Msg           FROM            (               SELECT  STCD, STNM, LGTD, LTTD, STLC, RZ AS zdt8, tmnow, ShowLevel                    FROM  ST_RSVR_D           ) t1            LEFT JOIN           (                  SELECT STCD, BGMD, EDMD, NVL(FSLTDZ, 0) AS WRZ, 0 AS GRZ                        FROM  ST_RSVRFSR_B           ) t2 ON t1.STCD = t2.STCD            AND t1.tmnow BETWEEN (select to_char(sysdate,'yyyy') from dual) || '/' || substr(t2.BGMD, 1,           2) || '/' || substr(t2.BGMD, 3, 2) AND (select to_char(sysdate,'yyyy') from dual) || '/' || substr(t2.EDMD, 1, 2) || '/' || substr(t2.EDMD, 3, 2))  t INNER JOIN(   SELECT     STCD, STTP, RVNM    FROM   ST_STBPRP_B    WHERE       STTP = 'ZZ')  tt ON t.STCD = tt.STCDWHERE t.Msg != '正常' AND  t.tmnow >= (select sysdate-1 from dual);