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);