10.6 自连接 · 波仔的 AI 编程实战课

📄 一句话概括

自连接就是「同一张表连两次」，靠两个不同的别名区分—— 不起别名写不出来，报 no such column。 三种经典用法：层级关系（员工和他的主管）、两两比较（同班同学）、 和上一条比（算环比）。 行数按平方涨：1000 行的表自连接最多 100 万行。 所以一定要先把范围缩到最小再加 a.id < b.id 这类去重条件。

📝 本章和前面几章的关系

前面四种连法都是「两张不同的表」，这一篇是「一张表当两张用」。 它的难点不在语法，在「怎么在脑子里把一张表看成两层」。 别名起得好不好，直接决定这段 SQL 能不能读。

一、为什么需要自连接

「找出同班的两个学生」——两边的数据都在 students 一张表里。 连接需要两张表，那就把同一张表「复制一份」来用，靠别名区分。 students a 和 students b 是同一张表的两个身份。 物理上只有一份数据，逻辑上当成两份来配。 a.id < b.id 那句是为了不重复： 不加的话「张伟-王芳」和「王芳-张伟」会出现两次，而且「张伟-张伟」这种自己配自己也会出现。 < 而不是 <=，就把「自己配自己」和「重复」一起排掉了。

二、不起别名就写不出来

FROM students JOIN students ON student.class_id = class_id 报 no such column。 同一张表出现两次，引擎没法知道你说的是哪一份。 所以「自连接必须起两个不同的别名」是硬要求，不是风格问题。 而且别名不能一样——两个都叫 s 同样报错。 别名要起得有意义：a／b 凑合， 员工／主管、原记录／新记录、上一行／这一行 更好读。 自连接本来就绕，别名是唯一的可读性抓手。

三、经典的三种用法

一是「层级关系」：员工表里有一个 manager_id 指向本表的 id， 「每个员工和他的主管」就得自连接——employees e JOIN employees m ON e.manager_id = m.id。 部门树、分类树、评论的回复链，都是这个形状。 二是「两两比较」：同班同学、同城用户、 「找出比某个人高的所有人」都是自连接。 注意这类需求行数按平方涨，一定要先限定范围。 三是「和上一条/下一条比」：自己跟自己错开一行连（ON a.id = b.id + 1）， 算环比、算相邻差值。 这种在时间序列里天天用。 严格说这类活窗口函数更合适 （第 6.5 篇），但老版本 MySQL 没有窗口函数，历史上都是自连接干的。

四、自连接的行数陷阱

18 对。 三个班各 4 人，每班「4 选 2」是 6 对，一共 18。 如果不加 a.id < b.id，就是 48 行（每班 4 × 4）， 每对出现两次、还多了 12 行「自己配自己」。 行数按平方涨： 1000 行的表自连接最多 100 万行。 所以自连接一定要先把范围缩到最小—— 先 where 限定班级或时间，再连。 这条纪律在层级查询里更要紧：一张 5000 人的员工表自连接就是 2500 万行， 加个 where 限定部门，可能瞬间降到几千行。

五、自连接能用子查询替代，但通常更慢

「同班同学」也能写成子查询： 先查出某人的班级号，再查同班的人。 逻辑更直白，但要对每个人各跑一次子查询。 自连接是一次配对搞定，子查询是逐行再查。 数据量大时差别很明显。 不过现在的优化器会把很多相关子查询改写成连接，所以别过早优化—— 先写清楚，测出来慢再改。 而且「写清楚」本身也有价值：三个月后回来看，子查询往往比自连接好懂。

六、动手 Demo：同班同学：两个别名、一次去重、一次行数陷阱

Demo 四段：用两个别名把 students 连起来找同班同学、故意不起别名撞一次错、再数一遍加与不加 a.id < b.id 的行数差。 第三段那个 18 和 48 的对照是这一篇的核心——它说明自连接的行数是平方级的。

import _q46 as Q
c = Q.con()

# 自连接：同一张表两个别名
Q.show(c, "SELECT a.name AS 学生甲, b.name AS 学生乙, "
          "a.class_id AS 班级 "
          "FROM students a JOIN students b "
          "ON a.class_id = b.class_id "
          "WHERE a.id < b.id LIMIT 5")

# 不起别名：写不出来
c.execute("SELECT COUNT(*) FROM students JOIN students "
          "ON student.class_id = class_id")

# 不加 a.id < b.id：48 行
Q.show(c, "SELECT COUNT(*) AS pairs FROM students a "
          "JOIN students b ON a.class_id = b.class_id "
          "WHERE a.id < b.id")

实跑输出：

--------------------------------------------------------------------
第五十七课：自连接——一张表跟自己连
--------------------------------------------------------------------

  为什么需要自连接
  **「找出同班的两个学生」——两边的数据都在 `students` 一张表里。**
  **连接需要两张表，那就把同一张表「复制一份」来用**，**靠别名区分。**
  SQL> SELECT a.name AS 学生甲, b.name AS 学生乙, a.class_id AS 班级 FROM students a
       JOIN students b ON a.class_id = b.class_id WHERE a.id < b.id LIMIT 5
  学生甲     学生乙     班级
  -----------------------------
  张伟       周涛       1
  张伟       杨帆       1
  张伟       王芳       1
  王芳       周涛       1
  王芳       杨帆       1
  （5 行）
  **`students a` 和 `students b` 是同一张表的两个身份。** **`a.id < b.id` 那句是为了不重复**——
  **不加的话「张伟-王芳」和「王芳-张伟」会出现两次，而且「张伟-张伟」这种自己配自己也会出现。**

  不起别名就写不出来
  SQL> SELECT COUNT(*) FROM students JOIN students ON student.class_id = class_id
  报错：no such column: student.class_id
  **报 `no such column`。** **同一张表出现两次，引擎没法知道你说的是哪一份**，
  **所以「自连接必须起两个不同的别名」是硬要求。**
  **别名还要有意义**：`a`／`b` 凑合，
  **`员工`／`主管`、`原记录`／`新记录` 更好读。** **自连接本来就绕，别名是唯一的可读性抓手。**

  经典的三种用法
  **一是「层级关系」**：员工表里有一个 `manager_id` 指向本表的 `id`，
  **「每个员工和他的主管」就得自连接**——`employees e JOIN employees m ON e.manager_id = m.id`。
  **二是「两两比较」**：同班同学、同城用户、**「找出比某个人高的所有人」都是自连接。**
  **三是「和上一条/下一条比」**：**自己跟自己错开一行连**（`ON a.id = b.id + 1`），
  **算环比、算相邻差值。** **这种在时间序列里天天用。**

  自连接的行数陷阱
  SQL> SELECT COUNT(*) AS pairs FROM students a JOIN students b ON a.class_id = b.class_id
       WHERE a.id < b.id
  pairs
  ------
  18
  （1 行）
  **18 对。** **三个班各 4 人，每班 `C(4,2) = 6` 对，一共 18。**
  **如果不加 `a.id < b.id`，就是 48 行（12 × 4）**，**每对出现两次、还多了 12 行「自己配自己」。**
  **行数按平方涨**：
  **1000 行的表自连接最多 100 万行。** **所以自连接一定要先把范围缩到最小**——
  **先 `where` 限定班级或时间，再连。**

  自连接能用子查询替代，但通常更慢
  **「同班同学」也能写成子查询**：
  **先查出某人的班级号，再查同班的人。** **逻辑更直白，但要对每个人各跑一次子查询。**
  **自连接是一次配对搞定，子查询是逐行再查。** **数据量大时差别很明显。**
  **不过现在的优化器会把很多子查询改写成连接，所以别过早优化**——**先写清楚，测出来慢再改。**

第一段五行：张伟和周涛、杨帆、王芳同班。 注意「张伟-王芳」只出现一次，靠 a.id < b.id 排掉了反向那一份。 第二段不起别名直接报错，no such column。 第三段 18 对：三个班各 6 对。 不加去重条件就是 48 行，每对两次、还有 12 行自己配自己。 后三段没有 SQL，讲三种经典用法、行数陷阱，以及和子查询的对比。

30 秒实验

自连接为什么必须起两个不同的别名？
students a JOIN students b ON a.class_id = b.class_id 不加 a.id < b.id 会得到几行？
「每个员工和他的主管」怎么写？这张表的什么列让自连接成为可能？

常见误区

自连接不起别名或起相同别名。 直接报 no such column，写不出来。
忘了加 a.id < b.id 这类去重条件。 每对出现两次，还多出「自己配自己」的行。
不限定范围就自连接。 行数按平方涨，5000 行的表能连出 2500 万行。

自测三问（点开对答案）

1. 自连接的本质是什么？ —— 把同一张表当成两张来配对，靠两个不同别名区分，物理上还是那一份数据。 2. `a.id < b.id` 这一句解决什么问题？ —— 去掉「反向重复」（张伟-王芳与王芳-张伟）和「自己配自己」，用 `<` 一次解决两个问题。 3. 自连接最该防的是什么？ —— 行数按平方涨。要先把范围用 `WHERE` 缩到最小，再连。

第 10 章完了： 集合运算、内连接、外连接、笛卡儿积、自连接，五种拼法都会了。 下一篇进第 11 章——视图，把一段查询存成一个「表」。

☐ （本篇完，下一篇 11.1 创建视图）