sql查询以一定的时间间隔获取数据 [英] sql query to fetch data with a time interval

问题描述

大家好，
我有一个数据表，其字段类型为日期时间，并且每分钟有3-5个条目，例如(23/08/2012 18:05:01,23/08/2012 18:05:15,23/08/2012 18:05:26,23/08/2012 18:05:44,23/08/2012 18:05:59)，现在我需要每5分钟获取一条记录
有谁能帮助我，我该怎么做
谢谢

Hi all,
I got a data table with a field of type date time and i got data like 3-5 entries for every minute ie(23/08/2012 18:05:01,23/08/2012 18:05:15,23/08/2012 18:05:26,23/08/2012 18:05:44,23/08/2012 18:05:59), now i need to get data one record for every 5th minute
can any one please help me how can i do this one
Thanks

推荐答案

select *
from
(
    select *,
       datepart(minute, yourDate) mn,
       row_number() over(partition by datepart(minute, yourDate) order by yourDate) rn
    from yourTable
) x
where (mn % 5) = 0
    and rn = 1

这篇关于sql查询以一定的时间间隔获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！