水利防汛之水庫和河道預警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);相關文章
- 1.4 資料庫和常用SQL語句（正文）——MySQL資料庫命令和SQL語句資料庫MySql
- 河道水位標尺識別預警系統
- 【資料庫】SQL語句資料庫SQL
- 水利遙測終端機（水利rtu）河道水質監測系統
- 資料庫常用的sql語句大全--sql資料庫SQL
- 資料庫常用操作SQL語句資料庫SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- sql 語句網路除錯和 sql 語句低層傳輸檢視SQL除錯
- MySql和簡單的sql語句MySql
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- SQL SELECT 語句SQL
- sql常用語句SQL
- idea內建資料庫 + sql語句庫表操作Idea資料庫SQL
- sql 正則替換資料庫語句！SQL資料庫
- 資料庫常用的sql語句彙總資料庫SQL
- GaussDB資料庫SQL系列-動態語句資料庫SQL
- 為什麼要庫存預警？如何庫存預警？
- Laravel 使用 sql 語句 和 sql 檔案 來建立執行資料庫遷移LaravelSQL資料庫
- SSH框架控制檯輸出HQL語句和SQL語句的方法框架SQL
- Oracle資料庫SQL語句執行過程Oracle資料庫SQL
- mysql資料庫sql語句基礎知識MySql資料庫
- MySQL資料庫中SQL語句分幾類?MySql資料庫
- MySQL資料庫：7、SQL常用查詢語句MySql資料庫
- 1.3. SQL 語句SQL
- Oracle基本SQL語句OracleSQL
- Sql語句小整理SQL
- SQL語句優化SQL優化
- SQL語句IN的用法SQL
- SQL 語句學習SQL
- Sql Server 資料庫獲取字串中小寫字母的SQL語句SQLServer資料庫字串
- Oracle SQL精妙SQL語句講解OracleSQL
- SQL Server 資料庫部分常用語句小結（二）SQLServer資料庫
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- SQL Server 資料庫部分常用語句小結（一）SQLServer資料庫
- MySQL學習筆記之SQL語句建立、修改和刪除資料庫MySql筆記資料庫