如何從SQL資料庫查詢中過濾掉值-有解無憂

我正在嘗試對歷史資料庫進行一些基本過濾，但不確定如何完成。我有下面的 SQL 代碼（洗掉了不相關的部分），并希望對其進行過濾，以便它僅顯示'FV215_57_STAT'等于 3 時的資料。

SET NOCOUNT ON 
DECLARE @StartDate DateTime 
DECLARE @EndDate DateTime 
SET @StartDate = '20211121 09:27:01.000' 
SET @EndDate = '20211221 09:27:01.000' 
SET NOCOUNT OFF 
SELECT  * FROM ( 
SELECT History.TagName, DateTime, Value, StartDateTime 
 FROM History 
 WHERE History.TagName IN ('FV215_57_STAT','FV215_58_STAT','FV317_28_STAT','FIC341_107_PV') 
 AND wwRetrievalMode = 'Cyclic' 
 AND wwResolution = 1800000
 AND wwVersion = 'Latest' 
 AND DateTime >= @StartDate 
 AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate

該代碼在 1 個月內每 30 分鐘檢索一個標簽名稱和一個值/日期時間。我試過

WHERE History.TagName = ('FV215_57_STAT')
 AND Value=3

這僅顯示值為 3 時的第一個字串。我'FV215_57_STAT'還需要過濾后面的三個標簽，以便它們僅在'FV215_57_STAT'等于 3 時顯示。這可能嗎？

編輯：下面顯示的 csv 中的原始資料片段。使用修改后的代碼，應洗掉前 4 個值（因為 FV215_57_STAT 為 1），接下來的 4 個值將顯示因為 FV215_57_STAT 為 3，等等。

FV215_57_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FV215_58_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FV317_28_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FIC341_107_PV,21/11/2021 09:57:01,1092.48620605469,21/11/2021 09:27:01
FV215_57_STAT,21/11/2021 10:27:01,3,21/11/2021 09:57:01
FV215_58_STAT,21/11/2021 10:27:01,1,21/11/2021 09:57:01
FV317_28_STAT,21/11/2021 10:27:01,1,21/11/2021 09:57:01
FIC341_107_PV,21/11/2021 10:27:01,1090.34826660156,21/11/2021 09:57:01
FV215_57_STAT,21/11/2021 10:57:01,3,21/11/2021 10:27:01
FV215_58_STAT,21/11/2021 10:57:01,1,21/11/2021 10:27:01
FV317_28_STAT,21/11/2021 10:57:01,3,21/11/2021 10:27:01
FIC341_107_PV,21/11/2021 10:57:01,1090.25109863281,21/11/2021 10:27:01
FV215_57_STAT,21/11/2021 11:27:01,3,21/11/2021 10:57:01
FV215_58_STAT,21/11/2021 11:27:01,1,21/11/2021 10:57:01
FV317_28_STAT,21/11/2021 11:27:01,3,21/11/2021 10:57:01
FIC341_107_PV,21/11/2021 11:27:01,1094.44519042969,21/11/2021 10:57:01
FV215_57_STAT,21/11/2021 11:57:01,3,21/11/2021 11:27:01
FV215_58_STAT,21/11/2021 11:57:01,1,21/11/2021 11:27:01
FV317_28_STAT,21/11/2021 11:57:01,3,21/11/2021 11:27:01
FIC341_107_PV,21/11/2021 11:57:01,1100.19775390625,21/11/2021 11:27:01
FV215_57_STAT,21/11/2021 12:27:01,3,21/11/2021 11:57:01
FV215_58_STAT,21/11/2021 12:27:01,1,21/11/2021 11:57:01
FV317_28_STAT,21/11/2021 12:27:01,3,21/11/2021 11:57:01
FIC341_107_PV,21/11/2021 12:27:01,1103.96740722656,21/11/2021 11:57:01
FV215_57_STAT,21/11/2021 12:57:01,3,21/11/2021 12:27:01
FV215_58_STAT,21/11/2021 12:57:01,1,21/11/2021 12:27:01
FV317_28_STAT,21/11/2021 12:57:01,3,21/11/2021 12:27:01
FIC341_107_PV,21/11/2021 12:57:01,1086.94787597656,21/11/2021 12:27:01
FV215_57_STAT,21/11/2021 13:27:01,3,21/11/2021 12:57:01
FV215_58_STAT,21/11/2021 13:27:01,1,21/11/2021 12:57:01
FV317_28_STAT,21/11/2021 13:27:01,3,21/11/2021 12:57:01
FIC341_107_PV,21/11/2021 13:27:01,1089.37548828125,21/11/2021 12:57:01
FV215_57_STAT,21/11/2021 13:57:01,3,21/11/2021 13:27:01
FV215_58_STAT,21/11/2021 13:57:01,1,21/11/2021 13:27:01
FV317_28_STAT,21/11/2021 13:57:01,3,21/11/2021 13:27:01
FIC341_107_PV,21/11/2021 13:57:01,1079.55786132813,21/11/2021 13:27:01
FV215_57_STAT,21/11/2021 14:27:01,3,21/11/2021 13:57:01
FV215_58_STAT,21/11/2021 14:27:01,1,21/11/2021 13:57:01
FV317_28_STAT,21/11/2021 14:27:01,3,21/11/2021 13:57:01
FIC341_107_PV,21/11/2021 14:27:01,1082.06420898438,21/11/2021 13:57:01
FV215_57_STAT,21/11/2021 14:57:01,3,21/11/2021 14:27:01
FV215_58_STAT,21/11/2021 14:57:01,1,21/11/2021 14:27:01
FV317_28_STAT,21/11/2021 14:57:01,3,21/11/2021 14:27:01
FIC341_107_PV,21/11/2021 14:57:01,1088.82287597656,21/11/2021 14:27:01
FV215_57_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FV215_58_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FV317_28_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FIC341_107_PV,21/11/2021 15:27:01,1172.87451171875,21/11/2021 14:57:01```

uj5u.com熱心網友回復：

我注意到資料以 4 組為一組（正如您的要求所暗示的那樣），并且所有行都具有相同的日期和時間，所以這樣的事情可能會起作用。我已經包含了我的演示示例資料，因為我仍然無法從您的問題中復制它（提示：將資料作為文本而不是影像發布）

declare @demo table (TagName varchar(30), [Datetime] datetime, [Value] int, StartDateTime datetime)
insert into @demo (TagName, [Datetime], [Value]) values
('FV215_57_STAT', '2022-03-01 10:00:00', 1),('FV215_58_STAT', '2022-03-01 10:00:00', 1),('FV215_28_STAT', '2022-03-01 10:00:00', 1),
('FV215_29_STAT', '2022-03-01 10:00:00', 1),('FV215_57_STAT', '2022-04-01 10:00:00', 3),
('FV215_58_STAT', '2022-04-01 10:00:00', 3),('FV215_28_STAT', '2022-04-01 10:00:00', 3),
('FV215_29_STAT', '2022-04-01 10:00:00', 3),('FV215_57_STAT', '2022-05-01 10:00:00', 2),
('FV215_58_STAT', '2022-05-01 10:00:00', 2),('FV215_28_STAT', '2022-05-01 10:00:00', 2),
('FV215_29_STAT', '2022-05-01 10:00:00', 2),('FV215_57_STAT', '2022-06-01 10:00:00', 3),
('FV215_58_STAT', '2022-06-01 10:00:00', 3),('FV215_28_STAT', '2022-06-01 10:00:00', 3),
('FV215_29_STAT', '2022-06-01 10:00:00', 3);

;with cte as
(
    select [Datetime] as dt
    from @demo
    where [Value] = 3 and TagName = 'FV215_57_STAT'
)
select * 
from cte
join @demo on dt=[Datetime];

我使用“通用表運算式”來獲取我的初始日期 - 請參閱https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql ?view=sql-server-ver15

這可能不是一個安全的假設，所以這里有一個替代方案

;with cte as
(
    -- Give every row a number
    select ROW_NUMBER() OVER (ORDER BY [DateTime]) as rn
    ,TagName, [Datetime],[Value]
    from @demo
), cte2 as
(
    -- get the row number of the items we want
    select cte.rn, cte.TagName, cte.Datetime, cte.Value
    from cte
    where [Value] = 3 and TagName = 'FV215_57_STAT'
)
select d.* 
from cte2 c
-- go back to the original cte to get the row number from cte2 and the following 3 rows
inner join cte d on c.rn = d.rn or c.rn   1 = d.rn or c.rn   2 = d.rn or c.rn   3 = d.rn
order by d.[Datetime]

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466141.html

標籤：sql sql服务器奇迹软件历史学家

上一篇：SQL更改UPDATE以跳過重復項

下一篇：如果兩列都不是空的，我如何獲得一個SQL'When'子句只回傳concat資料？