本文介绍了Co相关子组的Sql查询。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有车辆跟踪数据,我想创建车辆停止报告。帮我写那个sql查询。实际数据和结果数据分别如表1和表2所示。 表1
I've vehicle tracking data, I want to create Vehicle stoppage report. Help me to write sql query for that. Actual Data and resultant data are like Table1 and Table2 respectively. TABLE 1
VehicleId UnixTimeStamp Speed 1 17-01-2015 12:00:05 12 1 17-01-2015 12:00:06 0 1 17-01-2015 12:00:07 0 1 17-01-2015 12:00:08 0 1 17-01-2015 12:00:09 5 1 17-01-2015 12:00:10 8 1 17-01-2015 12:00:11 0 1 17-01-2015 12:00:12 0 1 17-01-2015 12:00:13 0 1 17-01-2015 12:00:14 7 2 17-01-2015 12:00:05 10 2 17-01-2015 12:00:06 0 2 17-01-2015 12:00:07 0 2 17-01-2015 12:00:08 0 2 17-01-2015 12:00:09 12 2 17-01-2015 12:00:10 0 2 17-01-2015 12:00:11 0 2 17-01-2015 12:00:12 10表2
TABLE 2
Vehicield Stopped From Stopped To Duration (Sec) 1 17-01-2015 12:00:06 17-01-2015 12:00:08 3 1 17-01-2015 12:00:11 17-01-2015 12:00:13 3 2 17-01-2015 12:00:06 17-01-2015 12:00:08 3 2 17-01-2015 12:00:10 17-01-2015 12:00:11 2推荐答案
实际上没有对此进行测试,但可能类似于这个: Haven't actually tested this but perhaps something like this: select start.unixtimestamp, stop.unixtimestamp from table1 start, table1 stop where start.speed = 0 and stop.vehicleid = start.vehicleid and stop.unixtimestamp > start.unixtimestamp and stop.speed = 0 and exists (select 1 from table1 prev where prev.vehicleid = start.vehicleid and prev.unixtimestamp = (select max(p2.unixtimestamp) from table1 p2 where p2.vehicleid = start.vehicleid and p2.unixtimestamp < start.unixtimestamp) and prev.speed > 0) and stop.unixtimestamp = (select max(p4.unixtimestamp) from table1 p4 where p4.unixtimestamp < (select min(p3.unixtimestamp) from table1 p3 where p3.vehicleid = stop.vehicleid and p3.unixtimestamp > start.unixtimestamp and p3.speed != 0))
编辑:纠正了一些错误
corrected few mistakes
我试过这个... I tried this... declare @t as table (vehicleid INT, unixtimestamp DATETIME, speed int) INSERT INTO @t VALUES(1, '2015-01-17 12:00:05', 12) INSERT INTO @t VALUES(1, '2015-01-17 12:00:06', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:07', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:08', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:09', 5) INSERT INTO @t VALUES(1, '2015-01-17 12:00:10', 8) INSERT INTO @t VALUES(1, '2015-01-17 12:00:11', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:12', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:13', 0) INSERT INTO @t VALUES(1, '2015-01-17 12:00:14', 7) INSERT INTO @t VALUES(2, '2015-01-17 12:00:05', 10) INSERT INTO @t VALUES(2, '2015-01-17 12:00:06', 0) INSERT INTO @t VALUES(2, '2015-01-17 12:00:07', 0) INSERT INTO @t VALUES(2, '2015-01-17 12:00:08', 0) INSERT INTO @t VALUES(2, '2015-01-17 12:00:09', 12) INSERT INTO @t VALUES(2, '2015-01-17 12:00:10', 0) INSERT INTO @t VALUES(2, '2015-01-17 12:00:11', 0) INSERT INTO @t VALUES(2, '2015-01-17 12:00:12', 10) select start.unixtimestamp, stop.unixtimestamp from @t start, @t stop where start.speed = 0 and stop.vehicleid = start.vehicleid and stop.unixtimestamp > start.unixtimestamp and stop.speed = 0 and exists (select 1 from @t prev where prev.vehicleid = start.vehicleid and prev.unixtimestamp = (select max(p2.unixtimestamp) from @t p2 where p2.vehicleid = start.vehicleid and p2.unixtimestamp < start.unixtimestamp) and prev.speed > 0) and not exists (select 1 from @t p3 where p3.vehicleid = start.vehicleid and p3.unixtimestamp > start.unixtimestamp and p3.unixtimestamp < stop.unixtimestamp and p3.speed > 0)
但结果不正确......两列如下:
but results are not correct...two columns like this:
unixtimestamp unixtimestamp 2015-01-17 12:00:06.000 2015-01-17 12:00:07.000 2015-01-17 12:00:06.000 2015-01-17 12:00:08.000 2015-01-17 12:00:11.000 2015-01-17 12:00:12.000 2015-01-17 12:00:11.000 2015-01-17 12:00:13.000 2015-01-17 12:00:06.000 2015-01-17 12:00:07.000 2015-01-17 12:00:06.000 2015-01-17 12:00:08.000 2015-01-17 12:00:10.000 2015-01-17 12:00:11.000
更多推荐
Co相关子组的Sql查询。
发布评论