水利防汛之水庫和河道預警SQL語句
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);相關文章
- 水利防汛抗旱知識普及篇
- 河道水位標尺識別預警系統
- 計訊物聯水庫洩洪監測預警系統，保障水庫安全度汛
- 水利遙測終端機（水利rtu）河道水質監測系統
- 小型水庫監測預警物聯網系統解決方案
- MySQL之SQL語句優化MySql優化
- Oracle之sql語句優化OracleSQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- 水庫大壩實時監測，推進洪澇災害預警治理
- MySQL指南之SQL語句基礎MySql
- Oracle SQL語句優化之UNIONOracleSQL優化
- MySQL管理之SQL語句例項MySql
- 使用預處理PreparedStatement執行Sql語句SQL
- 通用SQL語句修改欄位預設值SQL
- 1.4 資料庫和常用SQL語句（正文）——MySQL資料庫命令和SQL語句資料庫MySql
- SQL語句SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- sql 語句網路除錯和 sql 語句低層傳輸檢視SQL除錯
- oracle之PLSql語言（二）sql語句的使用OracleSQL
- SQL語句——DATA GUARD概念和管理SQL
- MySql和簡單的sql語句MySql
- 【轉】LINQ to SQL語句(1)之WhereSQL
- SQL語句基礎學習之TRIMSQL
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- flask之控制語句 if 語句與for語句Flask
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- SSH框架控制檯輸出HQL語句和SQL語句的方法框架SQL
- sql語句大全SQL
- 共享SQL語句SQL
- SQL語句整理SQL
- SQL基本語句SQL
- 精妙Sql語句SQL
- SQL語句集合SQL
- oracle sql語句OracleSQL
- sql語句 求救！SQL
- SQL精妙語句SQL
- SQL語句收集SQL
- 常用SQL語句SQL