我真的在为这个查询而苦苦挣扎。任何帮助将不胜感激!

表格:

+------+----------+--------------------+--------+-------------------+
|Ref   |Dept      |DeptTime            |Arr     |ArrTime            |
+------+----------+--------------------+--------+-------------------+
|1     |New York  |2015-02-01 08:00:00 |Boston  |2015-02-01 09:00:00|
|1     |Boston    |2015-02-01 10:00:00 |Chicago |2015-02-01 11:00:00|
|1     |Chicago   |2015-02-01 12:00:00 |Dallas  |2015-02-01 13:00:00|
|1     |Dallas    |2015-02-02 11:00:00 |Seattle |2015-02-02 13:00:00|
|2     |London    |2015-02-01 04:00:00 |Berlin  |2015-02-01 16:00:00|
|2     |Berlin    |2015-02-02 18:00:00 |Moscow  |2015-02-02 23:00:00|
+------+----------+--------------------+--------+-------------------+

此表显示多站旅程。如果到达和离开之间的停留时间少于 4 小时，则应视为同一行程的一部分。目的地应为下一次出发时间大于四小时的首次到达时间。在那种情况下，我想显示出发城市和出发时间，以及最终到达目的地和到达时间。您可以在此问题的底部看到所需的输出示例。

我认为查询应该做的是选择具有相同 ref 的值(使用连接？)，仅比较不同行的 ArrTime 和 DeptTime，如果差异大于 4 小时，则返回 Dept、DeptTime、多站旅程的 Arr 和 ArrTime。任何超过 4 小时的时间都被视为该旅程的结束。

我已经尝试使用几个简单的查询，但是我不知道如何计算具有相同 Ref 的不同项目之间的 Datetime 差异，以及如何比较不同行之间的 ArrTime 和 DeptTime。

我在 StackOverflow 上发现了这种比较日期时间的方法，但是我在使用它时得到的只是一个语法错误..

DECLARE @date1 DATETIME;
DECLARE @date2 DATETIME;

SET @date1 = '2012-04-01 08:10:16';
SET @date2 = '2012-04-10 11:35:36';

编辑:查询后的最终输出表应该是这样的:

+------+----------+--------------------+--------+-------------------+
|Ref   |Dept      |DeptTime            |Arr     |ArrTime            |
+------+----------+--------------------+--------+-------------------+
|1     |New York  |2015-02-01 08:00:00 |Dallas  |2015-02-01 13:00:00|
|1     |Dallas    |2015-02-02 11:00:00 |Seattle |2015-02-02 13:00:00|
|2     |London    |2015-02-01 04:00:00 |Berlin  |2015-02-01 16:00:00|
|2     |Berlin    |2015-02-02 18:00:00 |Moscow  |2015-02-02 23:00:00|
+------+----------+--------------------+--------+-------------------+

如有任何帮助，我们将不胜感激!

最佳答案

我会通过创建两个用于报告目的的中间表来做到这一点。

第一个称为 flight_journey。它根据您的时间要求将每个航类所在的航类分组。

第二个称为 first_last。它标识每个旅程中的第一个和最后一个航类，因为这就是您想要显示的全部内容。

最后的 select 语句仅使用 first_last 表和自连接来显示您想要的内容。

您可以索引中间表以提高性能。

FLIGHT_JOURNEY 表:

create table flight_journey as
select z.*, @row_num := if(@prev_value=chk,@row_num+1,1) as journey
from(
select
   f.*, p.arrtime as prev_arr,
   case
when p.depttime is null then 1
when ifnull(hour(timediff(f.depttime, p.arrtime)), 0) <= 4 then
   1
else
   0
end as chk
from
   flights f
left join flights p on f.ref = p.ref
and p.depttime = (select max(z.depttime) from flights z where z.ref = p.ref and z.depttime < f.depttime)
) z,
 (select @row_num := 1) x,
      (select @prev_value := '') y
order by
   z.ref,
   z.depttime;

FIRST_LAST 表:

create table first_last as
select
   y.*
from
   (select ref, journey, min(depttime) as min_dept, max(arrtime) as max_arr from flight_journey group by ref, journey) x
join flight_journey y on x.ref = y.ref
and x.journey = y.journey
and (x.min_dept = y.depttime or x.max_arr = y.arrtime);

最终选择语句:

select
   x.ref,
   x.dept,
   x.depttime,
   y.arr,
   y.arrtime
from
   first_last x
join first_last y on x.ref = y.ref
and x.journey = y.journey
and y.depttime > x.depttime
union all
   select
      x.ref,
      x.dept,
      x.depttime,
      x.arr,
      x.arrtime
   from
      first_last x
   join (select ref, journey from first_last group by ref, journey having count(*) = 1) y on x.ref = y.ref
   and x.journey = y.journey;

fiddle : http://sqlfiddle.com/#!2/f22b9/2/0

输出:

| REF |     DEPT |                        DEPTTIME |     ARR |                         ARRTIME |
|-----|----------|---------------------------------|---------|---------------------------------|
|   1 | New York | February, 01 2015 08:00:00+0000 |  Dallas | February, 01 2015 13:00:00+0000 |
|   1 |   Dallas | February, 02 2015 11:00:00+0000 | Seattle | February, 02 2015 13:00:00+0000 |
|   2 |   London | February, 01 2015 04:00:00+0000 |  Berlin | February, 01 2015 16:00:00+0000 |
|   2 |   Berlin | February, 02 2015 18:00:00+0000 |  Moscow | February, 02 2015 23:00:00+0000 |

关于mysql - 比较具有相同 ID 的行的日期时间，只返回日期时间差小于 4 小时的列，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28388826/