SQL 数据分析 · 指南 · 2026年10月30日 · 11 分钟阅读

SQL 自连接（Self Join）详解：配对、航班中转与历史记录，附例子

自连接（self join）就是一个普通的 JOIN，只不过两边是同一张表。 你把这张表写两次、起两个别名，比如 flights f1 JOIN flights f2，再用连接条件说明左边一行和右边一行是什么关系：同一个城市、行程的下一段、历史记录里的下一个版本。SQL 里没有 SELF JOIN 这个关键字，任何连接类型都可以用（INNER、LEFT，甚至 CROSS）。自连接回答的是同一张表里行与行之间的关系：配对、先后顺序、衔接。

所有例子都基于 Nomto SQL 课程的航空公司数据库（104 个机场、33,121 个航班、366,733 张机票），使用 PostgreSQL 语法，运行在 Nomto SQL 练习场所用的 DuckDB 引擎上。每张结果表都是真实输出。

自连接的语法

SELECT a.col, b.col
FROM some_table a
JOIN some_table b
  ON <how row a relates to row b>;

别名是必须的。没有别名，airports.city = airports.city 就有歧义，你也没法说明某一列来自哪一份副本。查询一旦超过几行，别名最好体现每份副本的角色（origin 和 dest、prev 和 next、out 和 back），而不只是 a 和 b。

如果你刷过力扣（LeetCode）的 SQL 题，「超过经理收入的员工」就是最典型的自连接：Employee e JOIN Employee m ON m.id = e.managerId，再比较 e.salary > m.salary。下面用航班数据把这个思路展开。

不重复的配对：用 a < b，而不是 a <> b

最简单的自连接是把有共同点的行配成对。SQL JOIN 详解里用它找出有多个机场的城市。这里换一个没有共同键的配对：按坐标计算大圆距离，找出不在同一城市、彼此距离最近的机场。

SELECT a1.airport_code, a1.city,
       a2.airport_code AS other, a2.city AS other_city,
       ROUND(2 * 6371 * ASIN(SQRT(
         POWER(SIN(RADIANS(a2.latitude - a1.latitude) / 2), 2) +
         COS(RADIANS(a1.latitude)) * COS(RADIANS(a2.latitude)) *
         POWER(SIN(RADIANS(a2.longitude - a1.longitude) / 2), 2)
       ))) AS km
FROM airports a1
JOIN airports a2 ON a1.airport_code < a2.airport_code
WHERE a1.city <> a2.city
ORDER BY km
LIMIT 6;

ON 里的条件决定了返回多少行。104 个机场时：

< 这个技巧适用于任何有固定顺序的列，比如 ID、代码、名称。它一次性去掉「自己配自己」和「镜像重复」，工作量也减半。只有方向有意义时才用 <>，比如「A 离 B 比离其他任何机场都近」，A 和 B 扮演的角色不同。这个查询把每个机场和其他所有机场比较，一百行没问题，但计算量随表大小的平方增长，所以在大表上要加一个缩小候选范围的条件（同一地区、纬度范围框）。

中转航班：一行的终点接上另一行的起点

最有用的自连接是把事件串起来：一个航班的到达，连接到稍后从同一机场出发的航班。8 月 20 日从喀山（KZN）经莫斯科多莫杰多沃（DME）中转、转机时间 1 到 4 小时的航班：

SELECT f1.flight_no AS leg1, f1.arrival_airport AS via,
       f1.scheduled_arrival::time AS lands,
       f2.flight_no AS leg2, f2.arrival_airport AS final_dest,
       f2.scheduled_departure::time AS departs,
       f2.scheduled_departure - f1.scheduled_arrival AS layover
FROM flights f1
JOIN flights f2
  ON  f2.departure_airport = f1.arrival_airport
  AND f2.scheduled_departure BETWEEN f1.scheduled_arrival + INTERVAL '1 hour'
                                 AND f1.scheduled_arrival + INTERVAL '4 hours'
  AND f2.arrival_airport <> f1.departure_airport
WHERE f1.departure_airport = 'KZN'
  AND f1.arrival_airport = 'DME'
  AND f1.scheduled_departure::date = '2026-08-20'
ORDER BY f2.scheduled_departure;

喀山的一班晚间航班可以继续飞往别尔哥罗德、南萨哈林斯克和哈巴罗夫斯克。这个查询写对靠三个细节：

• 连接不是按键。 f2.departure_airport = f1.arrival_airport 是等值条件，但时间窗口是范围条件。自连接经常两者混用。
• 时间窗口相对于第一段。 BETWEEN arrival + 1 hour AND arrival + 4 hours 排除了不可能的中转（还没落地就起飞）和不合理的中转（第二天才出发）。
• f2.arrival_airport <> f1.departure_airport 去掉了直接把你送回出发地的「中转」。

日期上的 WHERE 只作用于第一段；如果第一段很晚才落地，第二段可以是次日出发。去掉 f1.arrival_airport = 'DME' 这个条件，同一个查询会列出当天从喀山出发的全部 17 个中转组合，经停圣彼得堡、罗斯托夫、基洛夫和多莫杰多沃。要串联三段或更多，就重复 JOIN，或者改用递归 CTE。

往返行程：把一行和它的「镜像」配对

一张机票可以包含多个航段。往返票就是一张机票里有一段从 A 飞 B，后面又有一段从 B 飞 A。这是对机票航段的自连接，条件里把两个机场对调。为了简单起见，只看恰好两个航段的机票（共 183,338 张）：

WITH seg AS (
  SELECT tf.ticket_no, f.departure_airport, f.arrival_airport, f.scheduled_departure
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tf.ticket_no IN (
    SELECT ticket_no FROM ticket_flights GROUP BY ticket_no HAVING COUNT(*) = 2
  )
)
SELECT outbound.departure_airport AS home, outbound.arrival_airport AS destination,
       COUNT(*) AS round_trips,
       ROUND(AVG(back.scheduled_departure::date - outbound.scheduled_departure::date), 1) AS avg_days_away
FROM seg outbound
JOIN seg back
  ON  back.ticket_no = outbound.ticket_no
  AND back.departure_airport = outbound.arrival_airport
  AND back.arrival_airport = outbound.departure_airport
  AND back.scheduled_departure > outbound.scheduled_departure
GROUP BY home, destination
ORDER BY round_trips DESC
LIMIT 5;

在所有两航段机票中，有 177,847 张是往返票，旅客平均在外 8.4 天。条件 back.scheduled_departure > outbound.scheduled_departure 的作用和上面的 a < b 一样：没有它，每个往返行程还会「反向」匹配一次，把回程当成去程，数量就翻倍了。

为什么只看两航段的机票？带中转的机票，比如莫斯科经别尔哥罗德飞阿纳帕再飞回来，有四个航段，镜像条件会让每一段都和自己的回程匹配：一张票得到两对。只要一行有多个匹配，自连接就会让行数成倍增加，这和任何 JOIN 里的扇出是一回事（见 SQL JOIN 详解里「扇出」一节）。对自连接的结果做聚合之前，先想清楚输出的一行代表什么。

历史记录：把每个版本和下一个版本连起来

用 valid_from 和 valid_to 存版本的表，可以自连接，把每个版本和替代它的版本放在一起。下面是会员等级的变化，旧行的结束时间等于新行的开始时间：

SELECT prev.tier AS from_tier, nxt.tier AS to_tier, COUNT(*) AS moves
FROM loyalty_tier_history prev
JOIN loyalty_tier_history nxt
  ON  nxt.member_id = prev.member_id
  AND nxt.valid_from = prev.valid_to
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

这就是经典的「员工和经理」模式，只是用在了时间上：一行指向同一张表里的另一行，这里通过日期，而不是 manager_id 列。组织架构的写法是 JOIN employees mgr ON mgr.employee_id = emp.manager_id，用 LEFT JOIN 还能保留没有上级的 CEO。

自连接和窗口函数怎么选

等级表有 58,194 行、53,000 个会员，所以应该有 5,194 次等级变化。自连接只找到 4,989 次。用 LAG 问同一个问题，它只是按日期取上一行：

SELECT prev_tier AS from_tier, tier AS to_tier, COUNT(*) AS moves
FROM (
  SELECT tier,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
) t
WHERE prev_tier IS NOT NULL
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

5,194 次。自连接漏掉的 205 次，是 valid_to 和下一行 valid_from 对不上的行：110 个旧版本从未被关闭，95 个关闭晚了几天，和下一个版本重叠。按日期做等值连接会悄悄丢掉这些衔接；LAG 根本不看 valid_to。如何找出并修复这类行，见 SCD type 2 指南（英文）。

两个答案都不是「唯一正确」的。自连接信任记录里写明的衔接，窗口函数信任排序。两者之间的差异本身就是一次数据质量检查。经验法则：

窗口函数只读一遍表并排序；自连接读两遍，而且每行可能匹配到很多行。两者都能回答时，窗口函数通常更快，也不会扇出。LAG 和 LEAD 的详细用法见 SQL LAG 和 LEAD 函数和 SQL 窗口函数详解。

LEFT 自连接：保留没有配对的行

INNER 自连接会丢掉没有匹配的行。要保留它们，就用 LEFT JOIN，并检查右边是否为 NULL。当前等级「无来处」的行，也就是每段历史的第一行，就是没有前一个版本的行：

SELECT COUNT(*) AS rows_without_predecessor
FROM loyalty_tier_history cur
LEFT JOIN loyalty_tier_history prev
  ON  prev.member_id = cur.member_id
  AND prev.valid_to = cur.valid_from
WHERE prev.member_id IS NULL;

其中 53,000 行是会员的第一行。另外 205 行就是上一节里断开的衔接，这次没用窗口函数也找到了。这种「反自连接」专门用来找孤儿行：父分类不存在的分类、没有回程的航班、没有前一版本的版本。

常见错误

• 忘了不等式。 只写 a.city = b.city，每个机场都会和自己匹配；结果行数比原表还多，看起来每个机场都和别人共用城市。
• 该用 < 却用了 <>。 每一对出现两次，计数翻倍。
• 扇出之后再聚合。 如果一行能匹配多行，自连接上的 SUM 和 COUNT 会把它算好几次。
• 在可能为 NULL 的列上连接。 NULL = NULL 不为真，键为 NULL 的行永远找不到配对。上面 110 条未关闭的等级记录没能连上，原因就在这里。更多见 SQL NULL 值处理。
• 大表上没有缩小范围的条件。 条件太弱的自连接接近笛卡尔积：一百万行对一百万行。

常见问题

SQL 里的自连接是什么？

一张表和它自己做连接。你用两个不同的别名引用同一张表，再按某个条件把两份副本连起来，比如 FROM employees e JOIN employees m ON m.employee_id = e.manager_id。它用来比较同一张表里的行。

SELF JOIN 是关键字吗？

不是。自连接用普通的 JOIN、LEFT JOIN 或 CROSS JOIN 语法写；之所以叫自连接，只是因为两边是同一张表。

自连接怎么避免重复的配对？

用 a.id < b.id 代替 a.id <> b.id。这样既去掉了自己和自己的匹配，又让每对镜像只保留一个（保留 A–B，不要 B–A）。

什么时候用自连接，什么时候用 LAG 或 LEAD？

固定顺序下取上一行或下一行，用 LAG/LEAD。关系由条件而不是位置决定时，用自连接：一定距离内的配对、一定时间窗口内的中转、一行按 ID 指向另一行。

自连接可以是 LEFT JOIN 吗？

可以。LEFT 自连接会保留没有配对的行，右边填 NULL。再加上 WHERE 右边.key IS NULL，就变成反连接，能找出没有匹配的行，比如父记录缺失的记录。

其他语言： English, Español, Português (Brasil), Tiếng Việt