表格如下：
9:05
9:06
9:10
9:15
...
求任意一条记录上/下间隔
比如：9:10 上间隔4 下间隔5
希望得到下面的结果
9:05 0 1
9:06 1 4
9:10 4 5
... .. ..
------解决方案--------------------
declare @a table(a varchar(10))
insert @a select '9:05 '
union all select '9:06 '
union all select '9:10 '
union all select '9:15 '
select cast(a as datetime) a,id=(select count(1) from @a where a <=a.a) into # from @a a
select convert(varchar(5), a,108) a,
head=isnull((select datediff(n,a,a.a) from # where id=a.id-1),0),
back=isnull((select datediff(n,a.a,a) from # where id=a.id+1),0)
from # a
drop table #
------解决方案--------------------
create table T([time] varchar(10))
insert T select '9:05 '
union all select '9:06 '
union all select '9:10 '
union all select '9:15 '
select *,
上间隔=(select datediff(minute, isnull(max([time]), tmp.[time]), tmp.[time]) from T where [time] <tmp.[time]),
下间隔=(select datediff(minute, tmp.[time], isnull(min([time]), tmp.[time])) from T where [time]> tmp.[time])
from T as tmp
--result
time 上间隔 下间隔
---------- ----------- -----------
9:05 0 1
9:06 1 4
9:10 4 5
9:15 5 0
(4 row(s) affected)
------解决方案--------------------
declare @temp table(tid int identity(1,1), t varchar(10))
insert @temp(t) select t1.t from t1 order by t
最后句改下
select tb1.*,(case tb1.tid when 1 then 0 else datediff(minute,tb2.t,tb1.t) end) as 上间隔,(case tb3.tid when tb1.tid+1 then datediff(minute,tb1.t,tb3.t) else 0 end) as 下间隔 from @temp as tb1 left join @temp as tb2 on tb1.tid=tb2.tid+1
left join @temp as tb3 on tb1.tid=tb3.tid-1