MySql 时间表查询

Question

我有一个从原始数据输入的数据库表。它有一个火车列表，格式如下:

RI   TrainNo   TripNo   Location   ArrTime   DepTime
TS:  171       1        HMS                  17280
TE:  171       8        UPM        45360    
TS:  171       9        UPM                  53640
TE:  171       16       HMS        87120    

TS 是火车的起点，TE 是火车的终点。

我想确定火车是否在给定时间运行，因此运行以下查询。

SELECT DISTINCT tt.TrainNo
              , s1.StartTime
              , s1.StartTrip
              , s1.StartLocation
              , s2.StowTime
              , s2.StowTrip
              , s2.StowLocation 
           FROM `tt.data` tt 
           JOIN 
              ( SELECT TrainNo
                     , DepTime as StartTime
                     , TripNo as StartTrip
                     , Location as StartLocation 
                  FROM `tt.data` 
                 WHERE RI = 'TS:' 
              ) s1 
           JOIN 
              ( SELECT TrainNo
                     , ArrTime StowTime
                     , TripNo StowTrip
                     , Location StowLocation 
                  FROM `tt.data` 
                 WHERE RI = 'TE:' 
              ) s2 
             ON s1.TrainNo = tt.TrainNo 
            AND s1.TrainNo = s2.TrainNo 
            AND s1.StartTime < s2.StowTime
            AND 45350 > s1.StartTime 
            AND 45350 < s2.StowTime 
            AND tt.TrainNo = 171

输出:

TrainNo StartTime StartTrip StartLocation StowTime StowTrip StowLocation
171     17280     1         HMS           45360    8        UPM
171     17280     1         HMS           87120    16       HMS

第一行是正确的，火车在行程 1 和行程 8 之间运行。但是第二行不正确，如果时间大于 45360 那么它会告诉我火车在运行，而实际上它没有再次运行直到 53640。

删除此行的好方法是什么？

希望这是有道理的。

谢谢!

Answer 1

解决这个问题的关键是在每次旅行的开始和结束时正确地自助加入。一种方法是，对于给定的火车起点，搜索同一列火车最近的终点行程。您可以使用相关子查询来执行此操作，如下所示:

select 
    t1.trainno,
    t1.deptime  starttime,
    t1.tripno     starttrip,
    t1.location startlocation,
    t2.arrtime  stowtime,
    t2.tripno     stowtrip,
    t2.location stowlocation
from mytable t1
inner join mytable t2 
    on  t2.trainno = t1.trainno
    and t2.ri = 'TE:'
    and t2.arrtime = (select min(t3.arrtime) from mytable t3 where t3.arrtime > t1.deptime)
where 
    t1.ri = 'TS:'
    and t1.trainno = 171
    and t1.deptime < 45350
    and t2.arrtime > 45350

Demo on DB Fiddle :

trainno | starttime | starttrip | startlocation | stowtime | stowtrip | stowlocation------: | --------: | --------: | :------------ | -------: | -------: | :-----------    171 |     17280 |         1 | HMS           |    45360 |        8 | UPM