如何进行sql循环查询以检查值之间的差异? [英] How to make a sql loop query to check difference between values?

问题描述

首先，我不是英语为母语的人，这是一个很难解释的问题.如果您有任何疑问，请告诉我.

first of all, I'm not a native english speaker and this is a hard to explain issue. If you have any doubts, please let me know.

我们正在使用GPS车辆跟踪设备，该设备被编程为在不移动时每5分钟发送一次车辆的位置，并在移动时每100米发送并存储在数据库中的位置.

We're using a GPS vehicle tracking device, which is programmed to send and store in a DB the position of the vehicle every 5 min when not moving, and every 100 meters when moving.

该数据库具有一个名为"vehicle_gps"的表，该表存储数据，并具有诸如速度，位置，日期时间，vehicle_gps_id之类的值.

This DB has a table called "vehicle_gps" that stores the data, with values such as speed, position, datetime, vehicle_gps_id.

我需要某种查询，该查询将显示车辆停止超过特定时间(例如2分钟，丢弃交通信号灯)的不同位置.

I need some kind of query that will show the different positions where the vehicle stopped for more than a certain amount of time(e.g 2 minutes, discarding traffic lights).

例如，我需要一些可以告诉我的信息: 车辆在8:00处在Positon1(P1)上，从P1向P2方向行驶，在8.20处到达.它一直停留在P2处，直到10.20，然后在10.50处到达P3".

For instance, I need something that can tell me the following: "The vehicle was on Positon1 (P1) at 8:00, it left P1 towards P2, reaching it at 8.20. The vehicle stayed at P2 until 10.20 and it reached P3 at 10.50"

表中记录的示例

vehicle_gps_id  | datetime---------- | latitude | longitude | speed

1000------------| 05/16/2012 08:00:00|50.0000   |50.00000   |40 (km/h)
1001------------| 05/16/2012 08:01:00|51.0000   |51.00000   |38 (km/h)
1002------------| 05/16/2012 08:01:23|51.0045   |50.000054  |40 (km/h)
1003------------| 05/16/2012 08:01:40|51.00540  |51.0005430 |39 (km/h)
.
.
.
1040------------| 05/16/2012 08:20:40|53.00540  |53.0005430 |0 (km/h)
1041------------| 05/16/2012 08:25:40|53.00540  |53.0005430 |0 (km/h)
1042------------| 05/16/2012 08:30:40|53.00540  |53.0005430 |0 (km/h)
.
.
.
1060------------| 05/16/2012 10:20:40|53.00540|53.0005430|20 (km/h)

我该怎么做?

到目前为止，我能够得到固定位置之间的分钟差，因此，我想要一个循环来检查位置之间的所有日期，并在该差超过5分钟时中断.已经停止了.

So far, I was able to get the difference in minutes between fixed positions and for that reason, I wanted a loop that would check all dates between positions and break whenever that difference was longer than 5 minutes, which means that the vehicle had stopped.

TIA

推荐答案

DECLARE @idFrom as int,
        @idTo as int,
        @gpsDateFrom as datetime,
        @gpsDateTo as datetime
DECLARE VehicleCursor CURSOR FAST_FORWARD FOR 
SELECT  vehicle_gps_id, 
        datetimeCol
FROM    yourtable
ORDER BY vehicle_gps_id
OPEN VehicleCursor FETCH NEXT FROM VehicleCursor INTO @idFrom, @gpsDateFrom
    FETCH NEXT FROM VehicleCursor INTO @idTo, @gpsDateTo
    WHILE @@FETCH_STATUS = 0 BEGIN 
        IF DATEDIFF(MI,@gpsDateFrom,@gpsDateTo) >5
        BEGIN
            --Break (your code here)
        END
        SET @idFrom = @idTo
        SET @gpsDateFrom = @gpsDateTo
        FETCH NEXT FROM VehicleCursor INTO @idTo, @gpsDateTo
    END 
CLOSE VehicleCursor 
DEALLOCATE VehicleCursor

类似的事情应该对您有用.它是一个游标，仅遍历比较日期时间的所有列.您可以在if语句后的注释部分中输入要执行的操作.

Something like this should work for you. It is a cursor that just runs through all your columns comparing datetimes. You can enter in whatever you want to do into the commented section after the if statement.

这篇关于如何进行sql循环查询以检查值之间的差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！