找出时间间隔最小的记录
表格如下:
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 df

go

drop table temp0405
------
result
------
id df
--------------
3 60(seconds)


------解决方案--------------------
借用楼上测试数据:

select top 1 a.id ,min(datediff(s,a.time,b.time)) as tt from temp0405 a ,temp0405 b group by a.id
having min(datediff(s,a.time,b.time)) <> 0
order by tt desc