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

[andysun88]

如下表：
    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
*/

[andysun88]

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

[dawugui]

--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

[中国风]

[Quote=引用 2 楼 andysun88 的回复:]

但是id可能不是连续的啊！
[/Quote]

用

ID=row_number()over(order by [id] )生成一个连续的就行了

[中国风]

;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

[--小F--]

;with f as
(
select px=row_number()over(order by id),name,updatetime from tb
)

select
   *
from
   b t
where 
   exists(select 1 from b where name=t.name and id=t.id-1 and datediff(ss,updatetime , t.updatetime)>30)