先有一个表,结构如下:
id enterTime leaveTime
1 2007-3-28 00:00:00 2007-3-28 03:00:00
2 2007-3-28 03:20:00 2007-3-28 05:00:00
3 2007-3-28 05:40:33 2007-3-28 o8:00:00
我想得到离开上一个地点与进入下一个地点的时间差(enterTime-leaveTime),请问如何写sql?
id enterTime leaveTime
1 2007-3-28 00:00:00 2007-3-28 03:00:00
2 2007-3-28 03:20:00 2007-3-28 05:00:00
3 2007-3-28 05:40:33 2007-3-28 o8:00:00
我想得到离开上一个地点与进入下一个地点的时间差(enterTime-leaveTime),请问如何写sql?
from table a,(select * from table where id=a.id+1) b
order by a.id
insert aaaaa
select 1, '2007-3-28 00:00:00' , '2007-3-28 03:00:00' union all
select 2, '2007-3-28 03:20:00' , '2007-3-28 05:00:00' union all
select 3, '2007-3-28 05:40:33', '2007-3-28 08:00:00'
select id , DATEDIFF(minute,enterTime,leaveTime ) from aaaaa
drop table aaaaa
from #temp a inner join #temp b
on a.id=b.id-1
insert #temp
select 1, '2007-3-28 00:00:00' , '2007-3-28 03:00:00' union all
select 2, '2007-3-28 03:20:00' , '2007-3-28 05:00:00' union all
select 3, '2007-3-28 05:40:33', '2007-3-28 08:00:00'select a.id,
datediff(mi,a.leaveTime,b.enterTime) 分钟差
from #temp a inner join #temp b
on a.id=b.id-1----------------
id 分钟差
1 20
2 40
id user sensorName enterTime leaveTime
1 aa a1 2007-3-28 00:00:00 2007-3-28 03:00:00
6 aa a2 2007-3-28 03:20:00 2007-3-28 05:00:00
7 aa a3 2007-3-28 05:40:33 2007-3-28 08:00:00
9 aa a4 2007-3-28 09:00:00 2007-3-28 10:00:00
这样如何计算?
case when exists(select 1 from Table b where b.id = a.id +1)
then DATEDIFF(minute, a.leaveTime, (select enterTime from Table where id = a.id +1))
else null end 'timediff'
from Table as a查询结果
id timediff
1 20
2 40
3 NULL
select a.id,
case when
exists(select 1 from Table b where b.id =
(select min(id) from Table where user = 'UserName' and id > a.id ))
then DATEDIFF(minute, a.leaveTime, (select enterTime from Table where id =
(select min(id) from Table where user = 'UserName' and id > a.id )))
else null end 'timediff'
from Table as a
from #aaaaa a
group by id,leavetime