水利防汛之水库和河道预警SQL语句-阿里云开发者社区

水利防汛之水库和河道预警SQL语句

2013-03-29 912

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： SQL Server版本：create view v_HD_WarningInfoasSELECT t .

SQL Server版本：

create view v_HD_WarningInfo
as
SELECT     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 .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((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.STCD
WHERE     (t .Msg <> '正常') AND t .tmnow >= dateadd([day], - 3, GETDATE());




-------------------------------------------------------------------------------------------------
create view v_SK_WarningInfo
as
SELECT     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 .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((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.STCD
WHERE     (t .Msg <> '正常') AND t .tmnow >= dateadd([day], - 3, GETDATE());

Oracle版本：

--水库预警
create view v_SK_WarningInfo
as
SELECT     
  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 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'
)  tt
ON t .STCD = tt.STCD
WHERE 
t.Msg != '正常' AND t.tmnow >= (select sysdate-1 from dual);




--河道预警
create view v_HD_WarningInfo
as
SELECT 
    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.STCD
WHERE 
t.Msg != '正常' AND  t.tmnow >= (select sysdate-1 from dual);