某列在两条相邻的记录之间的差值

求某列在两条相邻的记录之间的差值，可扩展应用

求出符合条件的记录，以下语句是以时间作为差值条件

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