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

如下表：
    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秒就取两个的时间和名称

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[name] nvarchar(2),[updatetime] Datetime)
Insert #T
select 1,N'小明','2011-11-12 00:30:00' union all
select 2,N'小明','2011-11-12 00:40:00' union all
select 3,N'小明','2011-11-12 00:50:00' union all
select 4,N'小明','2011-11-12 01:30:00' union all
select 5,N'小明','2011-11-12 01:40:00' union all
select 6,N'小明','2011-11-12 01:50:00' union all
select 7,N'小明','2011-11-12 02:00:00' union all
select 8,N'小明','2011-11-12 03:00:00' union all
select 9,N'小明','2011-11-12 03:10:00' union all
select 10,N'小明','2011-11-12 03:20:00'
Go
Select 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    updatetime
3    小明    2011-11-12 00:50:00.000    2011-11-12 01:30:00.000
7    小明    2011-11-12 02:00:00.000    2011-11-12 03:00:00.000
*/

--sql 2000
select 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
) n
where m.px = n.px - 1 and datediff(ss,m.updatetime , n.updatetime) > 30

--sql 2005
select 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
) n
where m.px = n.px - 1 and datediff(ss,m.updatetime , n.updatetime) > 30

;with b
as
(
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