Co相关子组的Sql查询。

本文介绍了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