求SQL语句，取两条记录间隔5秒以上的数据集

wangji666666 2015-07-17 04:35:32

表结构
id datetime value
1 2015-05-05 08:00:00.000 0
2 2015-05-05 08:00:01.000 1
3 2015-05-05 08:00:02.000 2
4 2015-05-05 08:00:03.000 3
5 2015-05-05 08:00:04.000 4
6 2015-05-05 08:00:05.000 5
7 2015-05-05 08:00:06.000 6
8 2015-05-05 08:00:07.000 7
9 2015-05-05 08:00:08.000 8
10 2015-05-05 08:00:09.000 9
11 2015-05-05 08:00:10.000 10
12 2015-05-05 08:00:15.000 15
13 2015-05-05 08:00:19.000 19
14 2015-05-05 08:00:20.000 20
15 2015-05-05 08:00:30.000 30
16 2015-05-05 08:00:31.000 31
17 2015-05-05 08:00:36.000 36

要求取的数据之间间隔5秒以上
结果应该是这样
1 2015-05-05 08:00:00.000 0
6 2015-05-05 08:00:05.000 5
11 2015-05-05 08:00:10.000 10
12 2015-05-05 08:00:15.000 15
14 2015-05-05 08:00:20.000 20
15 2015-05-05 08:00:30.000 30
17 2015-05-05 08:00:36.000 36

后一个数据与前一个数据的间隔应该在5秒以上，得到这个数据后，再以这个数据为基础，再往后间隔5秒以上的第一个数据，依次类推。

...全文

550 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

wangji666666 2015-07-17

打赏
举报

为了验证各位答案的正确性，我加了2行数据 Id DT Value 1 2015-05-05 08:00:00.000 0 2 2015-05-05 08:00:01.000 1 3 2015-05-05 08:00:02.000 2 4 2015-05-05 08:00:03.000 3 5 2015-05-05 08:00:04.000 4 6 2015-05-05 08:00:05.000 5 7 2015-05-05 08:00:06.000 6 8 2015-05-05 08:00:07.000 7 9 2015-05-05 08:00:08.000 8 10 2015-05-05 08:00:09.000 9 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 13 2015-05-05 08:00:19.000 19 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 16 2015-05-05 08:00:31.000 31 17 2015-05-05 08:00:36.000 36 18 2015-05-05 08:00:40.000 40 19 2015-05-05 08:00:41.000 41 按照题意得到的结果应该是： Id DT Value 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 19 2015-05-05 08:00:41.000 41 @reenjie和@Landa_Alice执行的结果： 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 18 2015-05-05 08:00:40.000 40 @Cherise_huang执行的结果是： Id DT Value 1 2015-05-05 08:00:00.000 0 6 2015-05-05 08:00:05.000 5 11 2015-05-05 08:00:10.000 10 12 2015-05-05 08:00:15.000 15 14 2015-05-05 08:00:20.000 20 15 2015-05-05 08:00:30.000 30 17 2015-05-05 08:00:36.000 36 19 2015-05-05 08:00:41.000 41 所以Cherise_huang同志的答案是最符合题意的正确答案。谢谢大家的帮助！

Cherise_huang 2015-07-17

打赏
举报



create table dates(id INT,[DATETIME] DATETIME,VALUE int)

insert into dates 

select '1', '2015-05-05 08:00:00.000', '0' UNION all

select '2', '2015-05-05 08:00:01.000', '1' UNION all

select '3', '2015-05-05 08:00:02.000' ,'2' UNION all

select '4', '2015-05-05 08:00:03.000' ,'3' UNION all

select '5', '2015-05-05 08:00:04.000' ,'4' UNION all

select '6', '2015-05-05 08:00:05.000' ,'5' UNION all

select '7','2015-05-05 08:00:06.000' ,'6' UNION all

select '8', '2015-05-05 08:00:07.000' ,'7' UNION all

select '9', '2015-05-05 08:00:08.000', '8' UNION all

select '10', '2015-05-05 08:00:09.000' ,'9' UNION all

select '11', '2015-05-05 08:00:10.000' ,'10' UNION all

select'12', '2015-05-05 08:00:15.000' ,'15' UNION all

select '13', '2015-05-05 08:00:19.000' ,'19' UNION all

select'14', '2015-05-05 08:00:20.000' ,'20' UNION all

select'15', '2015-05-05 08:00:30.000' ,'30' UNION all

select'16','2015-05-05 08:00:31.000','31' UNION all

select'17', '2015-05-05 08:00:36.000','36'



DECLARE @Temp20150717 TABLE (DatetimeS DATETIME)



DECLARE @Sdate DATETIME,

		@Edate DATETIME

SET @Sdate=(SELECT MIN(Datetime) FROM dates)

SET @Edate=(SELECT MAX(Datetime) FROM dates)

WHILE @Sdate<=@Edate

BEGIN

	INSERT INTO @Temp20150717(DatetimeS)values (@Sdate)

	SET @Sdate=(SELECT MIN([Datetime]) FROM dates WHERE [Datetime]>=DATEADD(ss,5,@Sdate))

END



SELECT * FROM dates WHERE [Datetime] IN(SELECT DatetimeS from @Temp20150717)

结果如图;

Pact_Alice 2015-07-17

打赏
举报

;WITH cet AS ( SELECT 1 AS ID,'2015-05-05 08:00:00.000' AS shiftDate,0 AS Value UNION ALL SELECT 2 ,'2015-05-05 08:00:01.000',1 UNION ALL SELECT 3 ,'2015-05-05 08:00:02.000',2 UNION ALL SELECT 4 ,'2015-05-05 08:00:03.000',3 UNION ALL SELECT 5 ,'2015-05-05 08:00:04.000 ',4 UNION ALL SELECT 6 ,'2015-05-05 08:00:05.000',5 UNION ALL SELECT 7 ,'2015-05-05 08:00:06.000',6 UNION ALL SELECT 8 ,'2015-05-05 08:00:07.000',7 UNION ALL SELECT 9 ,'2015-05-05 08:00:08.000',8 UNION ALL SELECT 10 ,'2015-05-05 08:00:09.000 ',9 UNION ALL SELECT 11,'2015-05-05 08:00:10.000',10 UNION ALL SELECT 12 ,'2015-05-05 08:00:15.000',15 UNION ALL SELECT 13 ,'2015-05-05 08:00:19.000',19 UNION ALL SELECT 14 ,'2015-05-05 08:00:20.000',20 UNION ALL SELECT 15 ,'2015-05-05 08:00:30.000',30 UNION ALL SELECT 16 ,'2015-05-05 08:00:31.000',31 UNION ALL SELECT 17 ,'2015-05-05 08:00:36.000',36 ),cet1 AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY DATEDIFF(S,'2015-05-05 08:00:00.000',shiftDate)/5 ORDER BY cet.ID)AS N FROM cet ) SELECT * FROM cet1 WHERE N=1

reenjie 2015-07-17

打赏
举报


select Id,datetime,value from (
select *,
row_number() over(partition by DATEDIFF(SS,'2015-05-05 08:00:00.000',datetime)/5 order by datetime asc) as rId
from tablename as a)
) as b where rId=1

wangji666666 2015-07-17