求某列在两条相邻的记录之间的差值,可扩展应用
求出符合条件的记录,以下语句是以时间作为差值条件
1: 表变量的方法
declare @a table (tid int identity(1,1),id int,t1 datetime)
insert into @a(id,t1) select id,updatetime from book order by id
--select * from @a
select aaa.id,aaa.t1,bbb.id2,bbb.t2 from @a as aaa inner join(select tid as tid2,id as id2,t1 as t2 from @a ) as bbb on aaa.tid=bbb.tid2-1 where datediff(mi,aaa.t1,bbb.t2)>20
2:直接查询法,效率高
Select id,updatetime
from book a
where datediff(mi,updatetime,(select top 1 updatetime from book where id > a.id order by id) ) > 20
order by id