求计算行程时间的一个SQL语句
情景假设:
1.从A点可以到达B点
2.编号为1的车从A点到达B点
数据存储为两个表
tableA
ID--Time(经过A点的时间)
tableB
ID--Time(经过B点的时间)
我知道可以通过Datadiff求出两点的时间,但是由于车辆1可能多次经过AB,如何保证两点数据的正确配对?
例如:
tableA
ID Time
1 08:00
1 12:00
1 19:00
tableB
ID Time
1 09:15
1 13:10
1 20:20
执行SQL
SELECT a.id,a.[time] 'StartTime ',b.[time] 'ReachTime ',DATEDIFF(ss,a.[time],b.[time])/60 'TravelTime '
FROM tableA a,tableB b where a.id=b.id and a.time <b.time
--------------------结果-------------------------
1 1900-01-01 08:00:00.0001900-01-01 09:15:00.00075
1 1900-01-01 08:00:00.0001900-01-01 13:10:00.000310
1 1900-01-01 12:00:00.0001900-01-01 13:10:00.00070
1 1900-01-01 08:00:00.0001900-01-01 20:20:00.000740
1 1900-01-01 12:00:00.0001900-01-01 20:20:00.000500
1 1900-01-01 19:00:00.0001900-01-01 20:20:00.00080
怎样只选出正确的配对?
[解决办法]
--试一下
SELECT a.id,a.[time] 'StartTime ',b.[time] 'ReachTime ',DATEDIFF(ss,a.[time],b.[time])/60 'TravelTime '
FROM tableA a,tableB b where a.id=b.id and (select count(1) from tableA where id=a.id and [time] <a.[time])=(select count(1) from tableB where id=b.id and [time] <b.[time])
[解决办法]
select *,DATEDIFF(ss,StartTime, ReachTime)/60 as TravelTime
from
(
select a.ID , a.Time as StartTime, (select top 1 time from tableB b where a.id=b.id and Convert(datetime,a.time) <Convert(datetime,b.time) and
(select Count(1) from tableA c where a.id=c.id and Convert(datetime,a.time) <=Convert(datetime,c.time) and Convert(datetime,c.time) <convert(datetime,b.time))=1
) as ReachTime
from tableA a
)d
这样 (select Count(1) from tableA c where ……) = 1
是不是就好理解了？
这行主要是判断相邻的 tableB 里面的时间断内 只能有一个 tablA大于上一个时间 的时间存在 ，如果多于1 个就是你说的中间缺数据，置null
