mysql - 对具有相互关联的行的 SQL 表进行排序

Question

我有一个包含假日巴士行程时间的 SQL 表。该表结合了去程和回程(选项0为去程，选项1为回程)，还为用户提供了多种选择(选项2统计了选项:3次去程和2次回程)。每个行程可能跨越多行，因为该表列出了停靠点之间的所有内容:

• 去:

  • 选项 0:伦敦 -> 阿姆斯特丹 -> 柏林
  • 选项 1:伦敦 -> 苏黎世 -> 柏林
  • 选项 2:伦敦 -> 巴黎 -> 罗马 -> 柏林

• 返回

  • 选项 0:柏林 -> 阿姆斯特丹 -> 伦敦
  • 选项 1:柏林 -> 苏黎世 -> 伦敦

选项列显示行程是去还是回。Option2 列将选项匹配在一起。Option3 列显示每个选项的正确顺序。

+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| ID | DepartureDateTime   | ArrivalDateTime     | Departure        | Arrival        | Option       | Option2       | Option3       |
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| 72 | 2013-10-01 13:45:00 | 2013-10-02 16:40:00 | London           | Amsterdam      |            0 |             0 |             0 |
| 73 | 2013-10-02 17:35:00 | 2013-10-03 19:05:00 | Amsterdam        | Berlin         |            0 |             0 |             1 |
| 74 | 2013-10-01 17:00:00 | 2013-10-02 19:50:00 | London           | Zurich         |            0 |             1 |             0 |
| 75 | 2013-10-02 21:10:00 | 2013-10-03 22:40:00 | Zurich           | Berlin         |            0 |             1 |             1 |
| 76 | 2013-10-01 06:00:00 | 2013-10-02 08:40:00 | London           | Paris          |            0 |             2 |             0 |
| 77 | 2013-10-02 12:30:00 | 2013-10-03 14:05:00 | Paris            | Rome           |            0 |             2 |             1 |
| 78 | 2013-10-03 12:30:00 | 2013-10-04 14:05:00 | Rome             | Berlin         |            0 |             2 |             2 |
| 79 | 2013-10-10 14:50:00 | 2013-10-11 16:30:00 | Berlin           | Amsterdam      |            1 |             0 |             0 |
| 80 | 2013-10-11 17:05:00 | 2013-10-12 17:50:00 | Amsterdam        | London         |            1 |             0 |             1 |
| 81 | 2013-10-10 06:45:00 | 2013-10-11 08:25:00 | Berlin           | Zurich         |            1 |             1 |             0 |
| 82 | 2013-10-11 15:20:00 | 2013-10-12 16:05:00 | Zurich           | London         |            1 |             1 |             1 |
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+

我想要两个不同的查询:

1) 根据两件事对表进行排序: A。对于出发:行程的初始出发(离开伦敦)，不打乱后续停靠站的顺序。 b.返回:最后一次回程的到达(进入伦敦)，同样不会打乱后续站点的顺序。

2) 仅返回与特定日期/时间范围匹配的行程:初始出发(离开伦敦)和最终返回(进入伦敦)。例如，显示早上出发、晚上到达的行程。

如果您需要更多详细信息或者我遗漏了什么，请告诉我。

感谢您提前提供的帮助。

编辑 1

请阅读我的整篇文章。这里重要的是，行彼此相关。例如，下面的两行必须“在一起”，我正在处理的应用程序取决于顺序是否正确:

+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| ID | DepartureDateTime   | ArrivalDateTime     | Departure        | Arrival        | Option       | Option2       | Option3       |
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| 72 | 2013-10-01 13:45:00 | 2013-10-02 16:40:00 | London           | Amsterdam      |            0 |             0 |             0 |
| 73 | 2013-10-02 17:35:00 | 2013-10-03 19:05:00 | Amsterdam        | Berlin         |            0 |             0 |             1 |

这意味着无法按出发日期排序，因为行会混淆。

所以，如果我想根据出发地对上述行程进行排序，首先会出现伦敦经巴黎到柏林的行程，因为它在早上 6 点出发:

+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| ID | DepartureDateTime   | ArrivalDateTime     | Departure        | Arrival        | Option       | Option2       | Option3       |
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| 76 | 2013-10-01 06:00:00 | 2013-10-02 08:40:00 | London           | Paris          |            0 |             2 |             0 |
| 77 | 2013-10-02 12:30:00 | 2013-10-03 14:05:00 | Paris            | Rome           |            0 |             2 |             1 |
| 78 | 2013-10-03 12:30:00 | 2013-10-04 14:05:00 | Rome             | Berlin         |            0 |             2 |             2 |
| 72 | 2013-10-01 13:45:00 | 2013-10-02 16:40:00 | London           | Amsterdam      |            0 |             0 |             0 |
| 73 | 2013-10-02 17:35:00 | 2013-10-03 19:05:00 | Amsterdam        | Berlin         |            0 |             0 |             1 |

上面的部分表显示了排序结果的样子。基本上，排序算法应考虑初始出发的行并忽略排序中的其他行，但最终结果应在初始行程“下方”的行程中具有相关停靠点。

这听起来很可怕还是什么？

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

编辑2

根据要求，我使用的是 MySQL 5.1。

编辑3

成员@fancyPants已经解决了第一个查询。我做了一些修改，以考虑从 Option=0 到 Option=1 的更改:

SELECT 
`ID`, `DepartureDateTime`, `ArrivalDateTime`, `Departure`, `Arrival`, `Option`, `Option2`, `Option3`
FROM (
SELECT 
t.*,
CASE WHEN Option != @prev OR Option2 != @prev2 THEN @min_date := DepartureDateTime ELSE @min_date END as min_date,
CASE WHEN Option2 = @prev2 THEN @counter := @counter + 1 ELSE @counter := 0 END as counter,
@prev := Option, @prev2 := Option2
FROM Table1 t 
, (SELECT @min_date:=(SELECT DepartureDateTime FROM Table1 ORDER BY `Option`, Option2, Option3 LIMIT 1), @counter:=0, @prev:=NULL, @prev2:=NULL) vars
order by `Option`, Option2, Option3
) sq
ORDER BY min_date, counter

谢谢你的花哨裤子，非常棒的作品!

不幸的是，我对第二个查询还不够清楚。我需要的是建立在第一个查询之上(从而对结果进行排序)，然后根据日期时间范围限制结果。

Answer 1

这并不容易，这是我想到的(假设是 MySQL):

Sort the table based on two things: a. for outgoing: the initial depature of the trip (going out of London), without messing with the order of the subsequent stops:

SELECT 
`ID`, `DepartureDateTime`, `ArrivalDateTime`, `Departure`, `Arrival`, `Option`, `Option2`, `Option3`
FROM (
SELECT 
t.*,
CASE WHEN Option2 != @prev THEN @min_date := DepartureDateTime ELSE @min_date END as min_date,
CASE WHEN Option2 = @prev THEN @counter := @counter + 1 ELSE @counter := 0 END as counter,
@prev := Option2
FROM Table1 t 
, (SELECT @min_date:=(SELECT DepartureDateTime FROM Table1 ORDER BY `Option`, Option2, Option3 LIMIT 1), @counter:=0, @prev:=NULL) vars
order by `Option`, Option2, Option3
) sq
ORDER BY min_date, counter

for returning:

SELECT 
`ID`, `DepartureDateTime`, `ArrivalDateTime`, `Departure`, `Arrival`, `Option`, `Option2`, `Option3`
FROM (
SELECT 
t.*,
CASE WHEN Option2 != @prev THEN @min_date := ArrivalDateTime ELSE @min_date END as min_date,
CASE WHEN Option2 = @prev THEN @counter := @counter + 1 ELSE @counter := 0 END as counter,
@prev := Option2
FROM Table1 t 
, (SELECT @min_date:=(SELECT ArrivalDateTime FROM Table1 ORDER BY `Option`, Option2, Option3 LIMIT 1), @counter:=0, @prev:=NULL) vars
order by `Option`, Option2, Option3
) sq
ORDER BY min_date, counter

• Here's a sqlfiddle这表明了正确的行为。

对于你的第二个问题，如果我理解正确的话，你想要这样的东西:

SELECT 
t1.DepartureDateTime AS t1dep,
t2.ArrivalDateTime AS t2arr
, t1.*, t2.*
FROM Table1 t1
INNER JOIN Table1 t2 ON t1.Option = t2.Option AND t1.Option2 = t2.Option2 
WHERE t1.Option3 = (SELECT MIN(Option3) FROM Table1 t3 WHERE t1.Option = t3.Option AND t1.Option2 = t3.Option2)
AND t2.Option3 = (SELECT MAX(Option3) FROM Table1 t3 WHERE t1.Option = t3.Option AND t1.Option2 = t3.Option2)

AND t1.DepartureDateTime BETWEEN '2013-10-01 05:00:00' AND '2013-10-01 07:00:00'
AND t2.ArrivalDateTime BETWEEN '2013-10-04 14:00:00' AND '2013-10-04 15:00:00'

此查询返回出发的最短日期时间，即行程中第一个车站的出发日期和一条线路中最后一个车站的到达日期。然后你可以简单地调整where子句。

• another sqlfiddle在线查看结果

编辑:您在寻找这样的东西吗？

SELECT 
l.* FROM
(
SELECT 
`ID`, `DepartureDateTime`, `ArrivalDateTime`, `Departure`, `Arrival`, `Option`, `Option2`, `Option3`
,min_date, counter 
FROM (
SELECT 
t.*,
CASE WHEN `Option` != @prev OR Option2 != @prev2 THEN @min_date := DepartureDateTime ELSE @min_date END as min_date,
CASE WHEN Option2 = @prev2 THEN @counter := @counter + 1 ELSE @counter := 0 END as counter,
@prev := `Option`, @prev2 := Option2
FROM Table1 t 
, (SELECT @min_date:=(SELECT DepartureDateTime FROM Table1 ORDER BY `Option`, Option2, Option3 LIMIT 1), @counter:=0, @prev:=NULL, @prev2:=NULL) vars
order by `Option`, Option2, Option3
) sq
) l 
INNER JOIN
(SELECT `Option`, Option2 FROM Table1 WHERE DepartureDateTime BETWEEN '2013-10-02 11:30:00' AND '2013-10-02 13:00:00'
                          OR ArrivalDateTime BETWEEN '2013-10-03 14:00:00' AND '2013-10-03 14:15:00'
) r
ON l.`Option` = r.`Option` AND l.Option2 = r.Option2
ORDER BY min_date, counter

• 像往常一样sqlfiddle