sql2005 帮忙： 上下条记录的间隔时间超过30S的上下条记录时间集合,该如何解决

sql2005 帮忙： 上下条记录的间隔时间超过30S的上下条记录时间集合

SQL code

如下表：    id   name     updatetime     1   小明      2011-11-12 00:30:00     2   小明      2011-11-12 00:40:00     3   小明      2011-11-12 00:50:00     4   小明      2011-11-12 01:30:00     5   小明      2011-11-12 01:40:00     6   小明      2011-11-12 01:50:00     7   小明      2011-11-12 02:00:00     8   小明      2011-11-12 03:00:00     9   小明      2011-11-12 03:10:00     10   小明      2011-11-12 03:20:00得到的结果是：如下表：name        begindatetime             enddatetime小明      2011-11-12 00:50:00        2011-11-12 01:30:00小明      2011-11-12 02:00:00        2011-11-12 03:00:00条件：sql2005下、 就是上下两条记录的时间间隔大于30秒就取两个的时间和名称

SQL code

--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([id] int,[name] nvarchar(2),[updatetime] Datetime)Insert #Tselect 1,N'小明','2011-11-12 00:30:00' union allselect 2,N'小明','2011-11-12 00:40:00' union allselect 3,N'小明','2011-11-12 00:50:00' union allselect 4,N'小明','2011-11-12 01:30:00' union allselect 5,N'小明','2011-11-12 01:40:00' union allselect 6,N'小明','2011-11-12 01:50:00' union allselect 7,N'小明','2011-11-12 02:00:00' union allselect 8,N'小明','2011-11-12 03:00:00' union allselect 9,N'小明','2011-11-12 03:10:00' union allselect 10,N'小明','2011-11-12 03:20:00'GoSelect a.*,b.updatetime from #T as a    INNER JOIN #T AS b on a.name=b.name  and a.ID=b.ID-1 and DATEDIFF(n,a.[updatetime],b.[updatetime])>30/*id    name    updatetime    updatetime3    小明    2011-11-12 00:50:00.000    2011-11-12 01:30:00.0007    小明    2011-11-12 02:00:00.000    2011-11-12 03:00:00.000*/
[解决办法]
SQL code
--sql 2000select m.name , m.updatetime begindatetime , n.updatetime enddatetime from(  select t.* , px = (select count(1) from tb where updatetime < t.updatetime) + 1 from tb t) m , (  select t.* , px = (select count(1) from tb where updatetime < t.updatetime) + 1 from tb t) nwhere m.px = n.px - 1 and datediff(ss,m.updatetime , n.updatetime) > 30--sql 2005select m.name , m.updatetime begindatetime , n.updatetime enddatetime from(  select t.* , px = row_number() over(order by updatetime) from tb t) m , (  select t.* , px = row_number() over(order by updatetime) from tb t) nwhere m.px = n.px - 1 and datediff(ss,m.updatetime , n.updatetime) > 30
[解决办法]
探讨


但是id可能不是连续的啊！

[解决办法]
SQL code
;with bas(select ID=row_number()over(order by [id] ),[name],[updatetime] from #T)Select a.*,b.updatetime from b as a    INNER JOIN b on a.name=b.name  and a.ID=b.ID-1 and DATEDIFF(n,a.[updatetime],b.[updatetime])>30
[解决办法]
SQL code
;with f as(select px=row_number()over(order by id),name,updatetime from tb)select   *from   b twhere    exists(select 1 from b where name=t.name and id=t.id-1 and datediff(ss,updatetime , t.updatetime)>30)