表格如下:
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