Sql-Server
如何從表中的第一個值到最後一個值選擇日期時間步長 60 秒
我想從第一個日期開始向日期值添加 60 秒,如果數據存在於這樣的間隔中,則返回數據
結果如下:
我嘗試使用此程式碼但我沒有得到正確的結果
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY datetime ORDER BY DATETIME)- ROW_NUMBER() OVER (PARTITION BY dateadd(second,60,datetime) ORDER BY DATETIME) as g FROM Inputs ) SELECT DateTime FROM cte group by g
您可以將日期截斷為分鐘並按該值進行分區
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DATEADD(minute, DATEDIFF(minute, 0, t.datetime), 0) ORDER BY t.datetime) AS rn FROM YourTable t ) SELECT datetime, value FROM cte WHERE rn = 1;