如何按小时过滤sql server中的数据库数据

datates

如果我有如上图所示的数据,假设每分钟1天,并且我想将其过滤为每小时,那么我将有24个数据,因为我每小时要获取数据。怎么做

我已经尝试了一些查询,例如使用分组依据的查询,但是结果却不是我想要的。不适用于对数据进行分组。

   SELECT myDatetime FROM datates
  WHERE myDatetime >= '2020-03-01 05:30:00'
   AND myDatetime < DATEADD(DAY,1,'2020-03-01 07:30:00')
   group by myDatetime ,DATEPART(hour,myDatetime )
评论
  • xnam
    xnam 回复

    You can use row_number(). For instance, if you want the first value per hour:

    select d.*
    from (select d.*,
                 row_number() over (partition by convert(date, myDatetime), datepart(hour, myDatetime) order by mydatetime) as seqnum
          from datates d
         ) d
    where seqnum = 1;
    

    如果要对值求和,请使用聚合:

    select dateadd(hour, datepart(hour, myDatetime), convert(datetime, convert(date, myDatetime))),
           sum(nilai)
    from datates d
    group by dateadd(hour, datepart(hour, myDatetime), convert(datetime, convert(date, myDatetime)));