本文介绍了如何按给定间隔获取行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 这样一张桌子 日期编号 -------- ----------------------------------------- 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 15I want to get rows from this table by a time interval larger than 30sthose rows like this.Date Number-----------------------------------2012-05-26 10:03:11.000 12012-05-26 10:03:54.000 32012-05-26 10:04:33.000 62012-05-26 10:05:34.000 72012-05-26 10:03:11.000 12012-05-26 10:06:24.000 8Thanks推荐答案 SELECT * FROM dbo.t1 D_Date ID----------------------- -----------2012-05-26 10:03:11.000 12012-05-26 10:03:54.000 22012-05-26 10:04:15.000 32012-05-26 10:04:33.000 42012-05-26 10:04:57.000 52012-05-26 10:05:34.000 62012-05-26 10:05:46.000 72012-05-26 10:06:24.000 82012-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 bWHERE a.ID = b.ID - 1AND DATEDIFF(ss,a.D_Date , b.d_date) > 30 输出 OutputD_Date d_date----------------------- ----------------------- -----------2012-05-26 10:03:11.000 2012-05-26 10:03:54.000 432012-05-26 10:04:57.000 2012-05-26 10:05:34.000 372012-05-26 10:05:46.000 2012-05-26 10:06:24.000 38 CREATE TABLE T (D_Date Datetime)DECLARE @P_Date DatetimeDECLARE @D_Date Datetime -- database name DECLARE @D_Date1 DatetimeDECLARE @Sec intDELETE tDECLARE db_cursor CURSOR FOR SELECT D_Date FROM t1 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @D_DateSET @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_DateINSERT INTO T VALUES(@P_Date)END FETCH NEXT FROM db_cursor INTO @D_DateEND CLOSE db_cursor DEALLOCATE db_cursor b $ b SELECT * from T 输出为.... Output is ....D_Date-----------------------2012-05-26 10:03:11.0002012-05-26 10:03:54.0002012-05-26 10:04:33.0002012-05-26 10:05:34.0002012-05-26 10:06:24.000 这篇关于如何按给定间隔获取行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
10-28 21:40