如何按给定间隔获取行？ [英] how to get rows by a given interval ?

问题描述

这样一张桌子





日期编号

-------- -----------------------------------------

2012 -05-26 10：03：11.000 1

2012-05-26 10：03：54.000 3

2012-05-26 10：04：15.000 9

2012-05-26 10：04：33.000 6

2012-05-26 10：04：57.000 5

2012-05-26 10 ：05：34.000 7

2012-05-26 10：05：46.0​​00 9

2012-05-26 10：06：24.000 8

2012-05-26 10：06：34.000 15



我希望从这张表中获得大于30秒的时间间隔

这样的行。



日期编号

------------------ -----------------

2012-05-26 10：03：11.000 1

2012-05-26 10 ：03：54.000 3

2012-05-26 10：04：33.000 6

2012-05-26 10：05：34.000 7

2012-05-26 10 ：03：11.000 1

2012-05-26 10：06：24.000 8







谢谢

A table a like this


Date Number
-------------------------------------------------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 3
2012-05-26 10:04:15.000 9
2012-05-26 10:04:33.000 6
2012-05-26 10:04:57.000 5
2012-05-26 10:05:34.000 7
2012-05-26 10:05:46.000 9
2012-05-26 10:06:24.000 8
2012-05-26 10:06:34.000 15

I want to get rows from this table by a time interval larger than 30s
those rows like this.

Date Number
-----------------------------------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 3
2012-05-26 10:04:33.000 6
2012-05-26 10:05:34.000 7
2012-05-26 10:03:11.000 1
2012-05-26 10:06:24.000 8



Thanks

推荐答案

嗨Defygravity



我创建一个表（t1）和填写你提到的数据，所以现在我的表看起来像这样。

Hi Defygravity

I create a table (t1) and fill the data as you mentioned, So now my table is look like this.

SELECT * FROM dbo.t1

D_Date                  ID
----------------------- -----------
2012-05-26 10:03:11.000 1
2012-05-26 10:03:54.000 2
2012-05-26 10:04:15.000 3
2012-05-26 10:04:33.000 4
2012-05-26 10:04:57.000 5
2012-05-26 10:05:34.000 6
2012-05-26 10:05:46.000 7
2012-05-26 10:06:24.000 8
2012-05-26 10:06:34.000 9

(9 row(s) affected)

选择查询以查找......

Select Query to find......

SELECT a.D_Date , b.d_date, DATEDIFF(ss,a.D_Date , b.d_date) FROM t1 a, t1 b
WHERE a.ID = b.ID - 1
AND DATEDIFF(ss,a.D_Date , b.d_date) > 30

输出

Output

D_Date                  d_date
----------------------- ----------------------- -----------
2012-05-26 10:03:11.000 2012-05-26 10:03:54.000 43
2012-05-26 10:04:57.000 2012-05-26 10:05:34.000 37
2012-05-26 10:05:46.000 2012-05-26 10:06:24.000 38

Hi Defygravity



我在这里添加了一个光标。

Hi Defygravity

I added a cursor over here.

CREATE TABLE T (D_Date Datetime)

DECLARE @P_Date Datetime
DECLARE @D_Date Datetime -- database name  
DECLARE @D_Date1 Datetime
DECLARE @Sec int
DELETE t
DECLARE db_cursor CURSOR FOR  
SELECT  D_Date FROM t1 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @D_Date
	SET @P_Date = @D_Date 
	INSERT INTO T VALUES(@D_Date)
WHILE @@FETCH_STATUS = 0   
BEGIN  
PRINT @D_Date1 
		if (DATEDIFF(ss,@P_Date, @D_Date) >= 30)
			BEGIN 
				SET @P_Date = @D_Date
				INSERT INTO T VALUES(@P_Date)			
			END 
       
     FETCH NEXT FROM db_cursor INTO @D_Date
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

< br $> b $ b

SELECT * from T

输出为....

Output is ....

D_Date
-----------------------
2012-05-26 10:03:11.000
2012-05-26 10:03:54.000
2012-05-26 10:04:33.000
2012-05-26 10:05:34.000
2012-05-26 10:06:24.000

这篇关于如何按给定间隔获取行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！