> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL 自连接

> 如何把 MySQL 表与它自己连接以比较它自己的行，为什么查询需要 2 个别名，以及阻止每一对出现两次的条件。

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

自连接在同一个查询中把 1 个表读 2 次，这样您就能把它的行互相比较。它是回答成对问题的方法：哪些行共享一个值，哪一行比哪一行大。

开始之前，请用 `mysql -u root -p sakila` 打开客户端。

## 自连接的工作原理

```sql theme={null}
SELECT FIRST_ALIAS.COLUMN_NAME, SECOND_ALIAS.COLUMN_NAME
FROM TABLE_NAME AS FIRST_ALIAS
JOIN TABLE_NAME AS SECOND_ALIAS
  ON FIRST_ALIAS.COLUMN_NAME = SECOND_ALIAS.COLUMN_NAME;
```

把 `TABLE_NAME` 替换为表，`FIRST_ALIAS` 和 `SECOND_ALIAS` 替换为它的 2 个不同名字，每个 `COLUMN_NAME` 替换为要比较的列。

连接的其他一切都不变。服务器完全不知道 2 侧是同一个表，会像对待任何其他一对表那样对待它们。

## 别名不是可选的

没有别名时，查询把 2 个东西都叫 `actor`，无法区分它们。

```sql theme={null}
SELECT first_name FROM actor JOIN actor ON actor.last_name = actor.last_name LIMIT 1;
```

```text theme={null}
ERROR 1066 (42000): Not unique table/alias: 'actor'
```

`ERROR 1066` 是服务器在说，它需要为每一侧取一个名字。

## 示例

### 共享一个值的行

有些 Sakila 演员姓氏相同。这个查询把他们每个人与其他人配对。

```sql theme={null}
SELECT a1.first_name, a1.last_name, a2.first_name AS also_named
FROM actor AS a1
JOIN actor AS a2 ON a1.last_name = a2.last_name AND a1.actor_id < a2.actor_id
ORDER BY a1.last_name, a1.first_name, a2.first_name;
```

```text theme={null}
+-------------+-------------+-------------+
| first_name  | last_name   | also_named  |
+-------------+-------------+-------------+
| CHRISTIAN   | AKROYD      | DEBBIE      |
| CHRISTIAN   | AKROYD      | KIRSTEN     |
| KIRSTEN     | AKROYD      | DEBBIE      |
| CUBA        | ALLEN       | KIM         |
| CUBA        | ALLEN       | MERYL       |
| KIM         | ALLEN       | MERYL       |
...
+-------------+-------------+-------------+
108 rows in set (0.00 sec)
```

有 3 位演员姓 AKROYD，他们之间构成 3 对。

### 按数字比较行

同样的形式可以回答"哪些影片的时长与哪些影片完全相同"。这里把范围缩小到目录中最长的影片。

```sql theme={null}
SELECT f1.title, f2.title AS same_length, f1.length
FROM film AS f1
JOIN film AS f2 ON f1.length = f2.length AND f1.film_id < f2.film_id
WHERE f1.length = 185
ORDER BY f1.title, f2.title;
```

```text theme={null}
+--------------------+--------------------+--------+
| title              | same_length        | length |
+--------------------+--------------------+--------+
| CHICAGO NORTH      | CONTROL ANTHEM     |    185 |
| CHICAGO NORTH      | DARN FORRESTER     |    185 |
| CHICAGO NORTH      | GANGS PRIDE        |    185 |
...
+--------------------+--------------------+--------+
45 rows in set (0.00 sec)
```

有 10 部影片时长 185 分钟，而 45 是从 10 个东西中能组成的对数。

## 第二个条件在做什么

`a1.actor_id < a2.actor_id` 让每一对只保留一次，并阻止演员与自己配对。把它去掉，同一个查询回答的就是另一个问题。

```sql theme={null}
SELECT a1.first_name, a1.last_name, a2.first_name AS also_named
FROM actor AS a1
JOIN actor AS a2 ON a1.last_name = a2.last_name
ORDER BY a1.last_name, a1.first_name, a2.first_name;
```

```text theme={null}
+-------------+--------------+-------------+
| first_name  | last_name    | also_named  |
+-------------+--------------+-------------+
| CHRISTIAN   | AKROYD       | CHRISTIAN   |
| CHRISTIAN   | AKROYD       | DEBBIE      |
| CHRISTIAN   | AKROYD       | KIRSTEN     |
| DEBBIE      | AKROYD       | CHRISTIAN   |
| DEBBIE      | AKROYD       | DEBBIE      |
| DEBBIE      | AKROYD       | KIRSTEN     |
...
+-------------+--------------+-------------+
416 rows in set (0.00 sec)
```

416 行而不是 108 行，而且混进了 2 种行。`CHRISTIAN AKROYD` 与他自己配成了一对，因为他自己的行满足 `a1.last_name = a2.last_name`。另外 `CHRISTIAN` 与 `DEBBIE` 出现了，接着 `DEBBIE` 与 `CHRISTIAN` 也出现了，这是同一对反过来写。

用 `<` 比较 id 一次解决了这两个问题。它排除了行与自己匹配，因为没有任何 id 小于它自己；它还让每一对真正的配对只保留 1 种排列。当自连接是在寻找配对时，请始终使用 `<`。

## 总结

* 用自连接把 1 个表的行互相比较。
* 给这个表 2 个别名，因为没有别名您得到的就是 `ERROR 1066`。
* 当您想让每一对只出现一次时，用 `<` 比较 2 个别名的主键。
* 预期一行会与自己匹配，除非您排除这种情况。

## 另请参阅

* [表别名](https://villagesql.com/docs/zh/tutorial/table-aliases.md) — 这个查询所依赖的命名
* [INNER JOIN](https://villagesql.com/docs/zh/tutorial/inner-join.md) — 自连接是它的一种特例
* [连接的工作原理](https://villagesql.com/docs/zh/tutorial/joins.md) — 它在各种连接中的位置
* [MySQL JOIN 性能与常见错误](https://villagesql.com/docs/zh/guides/joins.md) — 自连接在大表上的代价


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.