I have an SQL table with holiday bus travel times. This table combines the outgoing trip and the return trip (Option 0 is going, Option 1 is returning), it also offers multiple choices to the user (Option2 counts the options: 3 going trips and 2 return trips). Each trip may span multiple rows, because the table lists all in between stops:
我有一个带有假日公共汽车旅行时间的SQL表。这张表结合了外出旅行和回程(选项0，选项1正在返回)，它也为用户提供了多种选择(Option2计算选项:3次旅行和两次回程)。每次访问可能跨越多个行，因为表中列出了所有的停止:
-
Going:
会:
- option 0: London -> Amsterdam -> Berlin
- 选项0:伦敦->阿姆斯特丹->柏林。
- option 1: London -> Zurich -> Berlin
- 选项1:伦敦->苏黎世->柏林。
- option 2: London -> Paris -> Rome -> Berlin
- 选项2:伦敦->巴黎->罗马->柏林。
-
Return
返回
- option 0: Berlin -> Amsterdam -> London
- 选项0:柏林->阿姆斯特丹->伦敦。
- option 1: Berlin -> Zurich -> London
- 选项1:柏林->苏黎世->伦敦。
The Option column shows if a trip is going or returning. The Option2 column matches the options together. The Option3 column shows the correct order of each option.
选项栏显示了访问或返回。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 |
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
I would like two different queries:
我想要两个不同的问题:
1) 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. b. for returning: the arrival of the final return trip (going into London), again without messing with the order of the subsequent stops.
1)根据两件事对表格进行排序:a. for外向:the first depature of the trip (going out of London)，不影响后续站点的顺序。b.返回:最终回程(进入伦敦)的到达，再次不影响后续站点的顺序。
2) Return only the trips that match a specific date/time range: initial departure (going out of London) and final return (going into London). For example, show trips that have a morning departure and an evening arrival.
2)只返回符合特定日期/时间范围的旅行:最初出发(离开伦敦)和最终返回(进入伦敦)。例如，显示有早晨出发和晚上到达的旅行。
Please let me know if you need more details or if I missed something.
如果你需要更多的细节，或者我错过了什么，请告诉我。
Thank you for your help in advance.
感谢您的帮助。
edit 1
编辑1
Please read my entire post. The important thing here, is that the rows are related to each other. For example, the two rows below MUST be "together", the application that I'm dealing with depends on the order to be correct:
请阅读我的全部帖子。这里重要的是，这些行是相互关联的。例如，下面的两行必须是“together”，我要处理的应用程序取决于要正确的顺序:
+----+---------------------+---------------------+------------------+----------------+--------------+---------------+---------------+
| 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 |
That means, sorting by the Departure date is not possible, because the rows would get mixed up.
这意味着，在离开日期之前排序是不可能的，因为行会被混淆。
So, if I wanted to sort the above trip based on the Departure, first would appear the London to Berlin via Paris trip because it departs at 6 in the morning:
所以，如果我想根据出发的情况来进行上述旅程，首先会出现在伦敦到柏林的巴黎之旅，因为它在早上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 |
The partial table above shows how the sorted result would look like. Basically, the sorting algorithm should take into consideration the row with the initial departure and ignore the other rows from the sorting, but the end result should have the related stops in the trip "below" the initial trip.
上面的部分表显示了排序结果的样子。基本上，排序算法应该考虑到初始离开时的行，忽略排序中的其他行，但是最终结果应该在“低于”初始行程的过程中有相关的停止。
Does that sound horrible or what?
听起来很恐怖吗?
Any help would be appreciated.
如有任何帮助，我们将不胜感激。
edit 2
编辑2
As requested, I am using MySQL 5.1.
按照要求，我使用的是MySQL 5.1。
edit 3
编辑3
The member @fancyPants has solved the first query. I made a slight modification to take into consideration the change from Option=0 to Option=1:
成员@fancyPants解决了第一个查询。我稍微修改了一下，考虑从选项=0到选项=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
thank you fancyPants, AMAZING WORK!
谢谢你，fancyPants，很棒的工作!
Unfortunately, I wasn't clear enough about the second query. What I need, is to built on top of the first query (thus results are sorted) and then limit results based on a date-time range.
不幸的是，我对第二个查询不够清楚。我需要的是构建在第一个查询之上(因此结果是排序的)，然后基于日期时间范围限制结果。
2 个解决方案
#1
2
This is not so easy, here's what I came up with (assuming MySQL):
这不是那么容易，这是我想到的(假设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:
根据以下两件事对表格进行排序:a. for外向:the first depature of the trip (going out of London)，而不影响后续站点的顺序:
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 which demonstrates correct behaviour.
- 这是一个展示正确行为的sqlfiddle。
For your second question, if I understood it correct you want something like this:
对于你的第二个问题，如果我理解正确你想要这样的东西:
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'
This query returns the minimum datetime of departure, meaning the departure date of the first station of a trip and the arrival date for the last station in one line. Then you can simply adjust where clause.
该查询返回出发时间的最小日期时间，这意味着一次访问的第一个站点的出发日期和最后一个站点的到达日期。然后你可以简单地调整where子句。
- another sqlfiddle to view results online
- 另一个在网上查看结果的sqlfiddle。
EDIT: Are you looking for something like this?
你在找这样的东西吗?
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
- as usual the sqlfiddle
- 像往常一样,sqlfiddle
#2
0
for 1 st question - going query
对于第一个问题——查询。
select * from time_table
where option = 0
order by DepartureDateTime, Option2, Option3;
1st question - returning query
第一个问题-返回查询。
select * from time_table
where option = 1
order by ArrivalDateTime, Option2, Option3;
This result base on understand what you asked. its not really clear.
这个结果基于你所问的。它不是清楚。
Please add some details for 2nd question. cant understand the issue. give example with data.
请为第二个问题添加一些细节。不能理解这个问题。给的示例数据。
#1
2
This is not so easy, here's what I came up with (assuming MySQL):
这不是那么容易，这是我想到的(假设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:
根据以下两件事对表格进行排序:a. for外向:the first depature of the trip (going out of London)，而不影响后续站点的顺序:
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 which demonstrates correct behaviour.
- 这是一个展示正确行为的sqlfiddle。
For your second question, if I understood it correct you want something like this:
对于你的第二个问题，如果我理解正确你想要这样的东西:
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'
This query returns the minimum datetime of departure, meaning the departure date of the first station of a trip and the arrival date for the last station in one line. Then you can simply adjust where clause.
该查询返回出发时间的最小日期时间，这意味着一次访问的第一个站点的出发日期和最后一个站点的到达日期。然后你可以简单地调整where子句。
- another sqlfiddle to view results online
- 另一个在网上查看结果的sqlfiddle。
EDIT: Are you looking for something like this?
你在找这样的东西吗?
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
- as usual the sqlfiddle
- 像往常一样,sqlfiddle
#2
0
for 1 st question - going query
对于第一个问题——查询。
select * from time_table
where option = 0
order by DepartureDateTime, Option2, Option3;
1st question - returning query
第一个问题-返回查询。
select * from time_table
where option = 1
order by ArrivalDateTime, Option2, Option3;
This result base on understand what you asked. its not really clear.
这个结果基于你所问的。它不是清楚。
Please add some details for 2nd question. cant understand the issue. give example with data.
请为第二个问题添加一些细节。不能理解这个问题。给的示例数据。