SQL 自连接示例：最快的中转航班

第 12.1 课 · 阅读时间：约 6 分钟 · 练习题 ↓

本课讲解 Bookings 数据库（PostgreSQL）上的 SQL 题目查找最快的航班的解法。你将看到自连接如何把一张 flights 表变成中转路线的两段航程，如何确保中转可行，以及如何选出最快的方案。

题目：找出 2017 年 8 月 16 日从圣彼得堡普尔科沃机场（LED）飞往布良斯克（BZK）、恰好中转一次的最快方案。对于每个方案，需要给出航班号、中转机场、每段航班的时长、候机时间以及总行程时间。时间根据 scheduled_departure 和 scheduled_arrival 计算。

请先尝试自己解答；完整的查询隐藏在本课末尾。

核心思路：一条路线就是两行

一次中转的路线不是一个航班，而是两个：

• 第 1 段：从 LED 飞往某个机场 X。
• 第 2 段：从同一个机场 X 飞往 BZK。

两段航程都是同一张 flights 表中的行。要把它们并排放在结果的一行中，就把这张表与自身连接，并给每个副本起一个别名：first_leg 和 second_leg。这就是自连接（self-join，见第 5.7 课）。

分步解答

1. 选出候选航班

第 1 段从 LED 出发，第 2 段到达 BZK，两段都在 8 月 16 日起飞：

SELECT COUNT(*) AS flights_from_led
FROM flights
WHERE departure_airport = 'LED'
  AND scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17';

结果：当天有 33 个航班从 LED 起飞；其中任何一个都可能是第 1 段。

2. 连接两段航程

两段航程必须在同一个机场衔接，并且第二个航班必须在第一个航班降落之后起飞：

SELECT
    first_leg.flight_no,
    first_leg.arrival_airport AS connection_airport,
    second_leg.flight_no,
    second_leg.scheduled_departure - first_leg.scheduled_arrival AS connection_time,
    second_leg.scheduled_arrival - first_leg.scheduled_departure AS total_trip_time
FROM flights AS first_leg
JOIN flights AS 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 total_trip_time;

结果：经由 3 个中转机场共有 18 个方案；最快的方案经莫斯科谢列梅捷沃机场（SVO）中转，总用时 1 小时 25 分钟。

• first_leg.arrival_airport = second_leg.departure_airport 是中转点。
• second_leg.scheduled_departure > first_leg.scheduled_arrival 排除了第二架飞机在第一架降落之前就起飞的方案。没有这个条件，查询会返回不可能实现的路线。

3. 计算时长

在 PostgreSQL 中，两个 timestamp 相减得到一个 interval，例如 01:25:00：

• 第一段航班：first_leg.scheduled_arrival - first_leg.scheduled_departure；
• 候机时间：second_leg.scheduled_departure - first_leg.scheduled_arrival；
• 总行程：second_leg.scheduled_arrival - first_leg.scheduled_departure，即从第一次起飞到最终到达。

4. 保留最快的方案

按总行程时间排序，并用 LIMIT 1 保留第一行。如果可能有多个方案用时相同，LIMIT 1 只会返回其中之一；要显示全部，可以改为与最小值比较，例如使用 RANK()（第 7.2 课）。

注意事项

• 中转时间只有 5 分钟。 在最快的方案中，第二个航班在第一个航班降落 5 分钟后就从 SVO 起飞。题目只要求第二个航班更晚起飞；真实的订票系统会要求最短中转时间，例如 second_leg.scheduled_departure >= first_leg.scheduled_arrival + INTERVAL '1 hour'。
• 两段航程都要按日期过滤。 如果没有 second_leg 上的日期条件，查询也会连接到之后几天的航班。
• BETWEEN 包含两端。 BETWEEN '2017-08-16' AND '2017-08-17' 也包含 8 月 17 日零点整起飞的航班。对于整天的范围，>= '2017-08-16' AND < '2017-08-17' 更精确。

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 total_trip_time
limit 1;
    

要点：

• 一次中转的路线是同一张表中的两行，用自连接和两个别名连接起来。
• 中转条件是 first_leg.arrival_airport = second_leg.departure_airport。
• 第二个航班必须在第一个航班到达之后起飞，否则路线不可行。
• 在 PostgreSQL 中，timestamp 相减得到 interval，可以比较和排序。
• ORDER BY ... LIMIT 1 返回最佳方案；如需保留并列的方案，请使用 RANK()。

常见问题

如何在 SQL 中查找中转航班？

把航班表与自身连接：第一个副本是第一段航程，第二个副本是第二段。连接条件是第一段的到达机场等于第二段的出发机场，并要求第二段的起飞时间晚于第一段的到达时间。

如何在 PostgreSQL 中计算两个 timestamp 之间的时间？

直接相减：scheduled_arrival - scheduled_departure 返回一个 interval。在 MySQL 中，使用 TIMESTAMPDIFF(MINUTE, start_time, end_time)。

如何查找中转两次或更多次的路线？

每多一段航程就再加一次自连接，或者使用递归 CTE，每次把路线延长一个航班（第 6.6 课）。