Sql-Server

如何從表中的第一個值到最後一個值選擇日期時間步長 60 秒

  • September 13, 2021

我想從第一個日期開始向日期值添加 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;

引用自:https://dba.stackexchange.com/questions/299489