分析 SQL 任务: 寻找有一次中转的航班
这个任务是使用 SQL 中最强大的工具之一来分析数据关系的一个优秀示例：将表连接到自身（自连接）。让我们逐步分解解决方案逻辑。
任务描述
我们需要找到从普尔科沃机场 (LED) 到布良斯克 (BZK) 的所有可能航班选项，这些航班包括恰好一次中转。
关键思想
一次中转的航线并不是一条，而是两条独立的航班通过一个中转机场连接在一起。
- 段落 1: 从 LED 出发到某个中转机场 C。
- 段落 2: 从同一个机场 C 出发到达 BZK。
我们的目标是找到所有这样的航班对，其中第一航班的到达机场与第二航班的出发机场匹配。
技术方法: 自连接
为了实现这个逻辑，我们将航班表视为两个不同的表。我们在脑海中为它创建两个别名，例如 first_leg（第一段）和 second_leg（第二段）。
步骤解决方案计划
定义航线的第一段。 我们使用别名
first_leg来查找所有从我们的起点出发的航班。- 条件:
first_leg.departure_airport = 'LED'。
- 条件:
定义航线的第二段。 使用别名
second_leg，我们查找所有到达我们目的地的航班。- 条件:
second_leg.arrival_airport = 'BZK'。
- 条件:
找到连接点（关键 JOIN 条件）。 现在最重要的事情是：我们需要将这两组航班连接起来。我们在条件上将它们结合在一起，即第一段的到达机场必须与第二段的出发机场完全匹配。
- 连接条件:
first_leg.arrival_airport = second_leg.departure_airport。 - 这个共同的机场就是我们所需的中转机场 (
connection_airport)。
- 连接条件:
检查中转时间： 我们还需要确保第二个航班在第一个航班到达 之后 起飞。这是通过以下条件确保的：
second_leg.scheduled_departure > first_leg.scheduled_arrival- 这个条件确保我们只考虑在航班之间有足够时间进行转机的连接。
形成结果。 在表连接后，我们在一行中拥有所有必要的信息。我们可以选择：
- 从
first_leg中选择出发机场 (departure_airport)。 - 中转机场（这是
first_leg.arrival_airport或等效的second_leg.departure_airport）。 - 从
second_leg中选择目的地机场 (arrival_airport)。
- 从
- 排序。 最后，我们只需要按中转机场字段对结果列表进行排序，以满足任务条件。
因此，自连接使我们能够 "展开" 一张表，并将其中的行相互匹配，从而找到复杂的多阶段航线。
剧透：解决方案的 SQL 查询隐藏在下面。点击以显示。
select
first_leg.flight_no flight1_no,
first_leg.departure_airport,
first_leg.arrival_airport connection_airport,
first_leg.scheduled_arrival - first_leg.scheduled_departure flight1_time,
second_leg.scheduled_departure - first_leg.scheduled_arrival connection_time,
second_leg.flight_no flight2_no,
second_leg.arrival_airport,
second_leg.scheduled_arrival - second_leg.scheduled_departure flight2_time,
(second_leg.scheduled_arrival - first_leg.scheduled_departure) total_trip_time
from flights first_leg
join flights second_leg on first_leg.arrival_airport=second_leg.departure_airport
and second_leg.arrival_airport = 'BZK'
and second_leg.scheduled_departure > first_leg.scheduled_arrival
where first_leg.departure_airport = 'LED'
and first_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
and second_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
order by (second_leg.scheduled_arrival - first_leg.scheduled_departure)
limit 1;