时间戳

首页 > 解决方案 > SQL 自联接表以查找与条件匹配的下一行

sql - SQL 自联接表以查找与条件匹配的下一行

问题描述

我有一个包含车辆跟踪器生成的事件的表。该表称为 tMain（因为它是我的主表），这里的重要列是 cVehicleId、cFixedId（事件 ID）和 cDateTime。ID 是整数，cDateTime 是 datetime2(7)。cFixedId 可以是 NEWTRIP (67)、DEPART (63)、STOP (64)、PROCEED (65) 和 DELIVER (66) 之一。这通常也是生成事件的顺序。该表包含由车队生成的事件。车辆中的跟踪器也提供日期时间，因此该表通常但不一定是 100%（因为跟踪器可能不是 100% 同步）按日期时间排序。每辆车的事件不会是连续的（因为有许多车辆并行报告），​​但它们将按时间排序。

示例（对于 1 辆车）：

cId         cDateTime               cVehicleId  cFixedId
62462946    2020-06-01 15:47:35.000  27         66
62462476    2020-06-01 15:37:58.000  27         65
62461602    2020-06-01 15:14:43.000  27         64
62461422    2020-06-01 15:11:08.000  27         63
62461407    2020-06-01 15:10:47.000  27         67

我想要的是一个返回所有 DELIVER 事件以及前面的 DEPART（或者可能是 NEWTRIP）事件的查询，以查看行程花费了多长时间。这应该看起来像这样：

cVehicleId  cFixedId  cDateTime                cFixedId  cDateTime
27          67        2020-06-01 15:10:47.000  66        2020-06-01 15:47:35.000

我尝试的是查找所有 DEPART 事件，然后使用具有 EventId DELIVER 的相同 VehicleId 搜索下一个事件。或者查找所有 DELIVER 事件并向后搜索具有相同 VehicleId 的最近的 DEPART 事件。

我一直在尝试很多使用自连接的查询，也考虑使用 LEAD 或 LAG 函数，但我认为这些对我没有帮助。我只是无法让它工作。

我目前拥有的是：

DECLARE @DEPART int = 63;
DECLARE @DELIVER int = 66;

DECLARE @StartDate  DATE = '2020-05-01';
DECLARE @StopDate   DATE = '2020-05-02';

select * from
(select cVehicleid, cDatetime, cFixedId from tMain where cFixedId = @DEPART and cDateTime > @StartDate and cDateTime < @StopDate) as t1
inner join 
(select top(1) cVehicleId, cDatetime, cFixedId from tMain where cFixedId = @DELIVER and cDateTime > @StartDate and cDateTime < @StopDate order by cDateTime) as t2 on t2.cVehicleId = t1.cVehicleId and t2.cDateTime > t1.cDateTime

但是，这不会返回任何内容。

我没有看到我做错了什么，我不知道如何继续。也许还有我不知道的更好的方法。我在网上做了很多搜索，但没有找到任何让我找到解决方案的东西。谁能给我一个提示？

不太重要的额外内容：如果结果按 VehicledId 分组会很好，但这不是绝对必须的。

标签： sqlsql-serverself-join

解决方案

我想要的是一个返回所有 DELIVER 事件以及前面的 DEPART（或者可能是 NEWTRIP）事件的查询，以查看行程花费了多长时间。

如果我理解正确，您可以使用apply：

select d.*, previous.*
from tmain d outer apply
     (select top (1) e.*
      from tmain e
      where e.cVehicleId = d.cVehicleId and
            e.cFixedId in ('DEPART', 'NEWTRIP') and
            e.cDateTime < d.cDateTime
      order by e.cDateTime desc
     ) previous
where d.ceventid = 'DELIVER';

为了清楚起见，这使用了字符串版本。

推荐阅读