时间戳

首页 > 解决方案 > MySql 时间表查询

mysql - MySql 时间表查询

问题描述

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

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。

删除这条线的好方法是什么？

希望这是有道理的。

谢谢！

标签： mysqlsqljoin

解决方案

例如：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(TripNo   SERIAL PRIMARY KEY
,RI CHAR(2) NOT NULL
,TrainNo INT NOT NULL 
,Location CHAR(3) NOT NULL
,ArrTime INT NULL
,DepTime INT NULL
);

INSERT INTO my_table VALUES
( 1,'TS',171,'HMS',NULL,17280),
( 8,'TE',171,'UPM',45360,NULL),
( 9,'TS',171,'UPM',NULL,53640),
(16,'TE',171,'HMS',87120,NULL);

SELECT a.*
     , b.*
  FROM 
     ( SELECT x.* 
            , MIN(y.tripno) y_trip 
         FROM my_table x 
         JOIN my_table y 
           ON y.tripno > x.tripno 
          AND y.trainno = x.trainno 
          AND y.ri = 'te' 
        WHERE x.ri = 'ts' 
        GROUP 
           BY x.tripno
     ) a
  JOIN my_table b
    ON b.tripno = a.y_trip
 WHERE 45350 BETWEEN a.deptime AND b.arrtime;

+--------+----+---------+----------+---------+---------+--------+--------+----+---------+----------+---------+---------+
| TripNo | RI | TrainNo | Location | ArrTime | DepTime | y_trip | TripNo | RI | TrainNo | Location | ArrTime | DepTime |
+--------+----+---------+----------+---------+---------+--------+--------+----+---------+----------+---------+---------+
|      1 | TS |     171 | HMS      |    NULL |   17280 |      8 |      8 | TE |     171 | UPM      |   45360 |    NULL |
+--------+----+---------+----------+---------+---------+--------+--------+----+---------+----------+---------+---------+

推荐阅读