表格如下:
id time num
01 9:01 ...
01 9:05 ..
01 9:12 ..
02 9:01 ..
03 9:01 ..
03 9:02 ..
04 9:06 ..
04 9:08 ..
.. .. ..希望找到同一ID的第二条记录与第一条记录之间间隔最短的ID
比如ID=03 间隔为1分钟.
id time num
01 9:01 ...
01 9:05 ..
01 9:12 ..
02 9:01 ..
03 9:01 ..
03 9:02 ..
04 9:06 ..
04 9:08 ..
.. .. ..希望找到同一ID的第二条记录与第一条记录之间间隔最短的ID
比如ID=03 间隔为1分钟.
create table temp0405(id int,time datetime)
go
insert temp0405
select 01,'9:01'
union all select 01,'9:05'
union all select 01,'9:12'
union all select 02,'9:01'
union all select 03,'9:01'
union all select 03,'9:02'
union all select 04,'9:06'
union all select 04,'9:08'
go
select top 1 a.id,datediff(s,a.time,b.time)as df from temp0405 a join temp0405 b on a.id=b.id and a.time<b.time order by dfgo drop table temp0405
------
result
------
id df
--------------
3 60(seconds)
having min(datediff(s,a.time,b.time))<>0
order by tt desc