> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-4/zi-lian-jie.md). # 5.4 自连接 在本教程中，您将了解如何使用连接语句将表连接到表自身，即，在同一张表上自己连接自己。 在之前的教程中，已经学习了如何使用[INNER JOIN](http://www.yiibai.com/mysql/inner-join.html)，[LEFT JOIN](http://www.yiibai.com/mysql/left-join.html) 或 [CROSS JOIN](http://www.yiibai.com/mysql/cross-join.html)子句将表连接到其他表。 但是，有一个特殊情况，需要将表自身连接，这被称为自连接。 当您想将表中行与同一表中的其他行组合时，可以使用自连接。要执行自联接操作必须使用[表别名](http://www.yiibai.com/mysql/alias.html)来帮助MySQL在单个查询中区分左表与同一张表的右表。 ## MySQL自连接的例子 我们来看看[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`employees`表，其表结构如下所示 - 要获得整个组织结构，可以使用`employeeNumber`和`reportsTo`列将`employees`表连接自身。`employees`表有两个角色：一个是经理，另一个是直接报告者(即，下属员工)。 ```sql SELECT CONCAT(m.lastname, ', ', m.firstname) AS 'Manager', CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report' FROM employees e INNER JOIN employees m ON m.employeeNumber = e.reportsto ORDER BY manager; ``` 执行上面查询，得到以下结果 - ```sql +--------------------+--------------------+ | Manager | Direct report | +--------------------+--------------------+ | Bondur, Gerard | Jones, Barry | | Bondur, Gerard | Castillo, Pamela | | Bondur, Gerard | Bondur, Loui | | Bondur, Gerard | Bott, Larry | | Bondur, Gerard | Gerard, Martin | | Bondur, Gerard | Hernandez, Gerard | | Bow, Anthony | Vanauf, George | | Bow, Anthony | Patterson, Steve | | Bow, Anthony | Thompson, Leslie | | Bow, Anthony | Tseng, Foon Yue | | Bow, Anthony | Firrelli, Julie | | Bow, Anthony | Jennings, Leslie | | Murphy, Diane | Firrelli, Jeff | | Murphy, Diane | Patterson, Mary | | Nishi, Mami | Kato, Yoshimi | | Patterson, Mary | Bow, Anthony | | Patterson, Mary | Patterson, William | | Patterson, Mary | Bondur, Gerard | | Patterson, Mary | Nishi, Mami | | Patterson, William | Marsh, Peter | | Patterson, William | King, Tom | | Patterson, William | Fixter, Andy | +--------------------+--------------------+ 22 rows in set ``` 在上述输出中，只能看到有经理的员工。 但是，由于`INNER JOIN`子句，所以看不到总经理。总经理是没有任何经理的员工，或者他的经理人是`NULL`。 我们将上述查询中的`INNER JOIN`子句更改为`LEFT JOIN`子句，以包括总经理。 如果管理员名称为`NULL`，则还需要使用[IFNULL](http://www.yiibai.com/mysql/ifnull.html)函数来显示总经理。 ```sql SELECT IFNULL(CONCAT(m.lastname, ', ', m.firstname), 'Top Manager') AS 'Manager', CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report' FROM employees e LEFT JOIN employees m ON m.employeeNumber = e.reportsto ORDER BY manager DESC; ``` 执行上面查询，得到以下结果 - ```sql +--------------------+--------------------+ | Manager | Direct report | +--------------------+--------------------+ | Top Manager | Murphy, Diane | | Patterson, William | Fixter, Andy | | Patterson, William | Marsh, Peter | | Patterson, William | King, Tom | | Patterson, Mary | Bondur, Gerard | | Patterson, Mary | Nishi, Mami | | Patterson, Mary | Bow, Anthony | | Patterson, Mary | Patterson, William | | Nishi, Mami | Kato, Yoshimi | | Murphy, Diane | Patterson, Mary | | Murphy, Diane | Firrelli, Jeff | | Bow, Anthony | Tseng, Foon Yue | | Bow, Anthony | Firrelli, Julie | | Bow, Anthony | Jennings, Leslie | | Bow, Anthony | Vanauf, George | | Bow, Anthony | Patterson, Steve | | Bow, Anthony | Thompson, Leslie | | Bondur, Gerard | Bott, Larry | | Bondur, Gerard | Gerard, Martin | | Bondur, Gerard | Hernandez, Gerard | | Bondur, Gerard | Jones, Barry | | Bondur, Gerard | Castillo, Pamela | | Bondur, Gerard | Bondur, Loui | +--------------------+--------------------+ 23 rows in set ``` 通过使用MySQL自连接，可以通过将`customers`表连接自身来显示位于同一个城市的客户列表。参考以下查询语句 - ```sql SELECT c1.city, c1.customerName, c2.customerName FROM customers c1 INNER JOIN customers c2 ON c1.city = c2.city AND c1.customername > c2.customerName ORDER BY c1.city; ``` 执行上面查询语句，得到以下结果 - ```sql +---------------+------------------------------+--------------------------------+ | city | customerName | customerName | +---------------+------------------------------+--------------------------------+ | Auckland | Kelly's Gift Shop | Down Under Souveniers, Inc | | Auckland | Kelly's Gift Shop | GiftsForHim.com | | Auckland | GiftsForHim.com | Down Under Souveniers, Inc | | Boston | Gifts4AllAges.com | Diecast Collectables | | Brickhaven | Collectables For Less Inc. | Auto-Moto Classics Inc. | | Brickhaven | Online Mini Collectables | Auto-Moto Classics Inc. | | Brickhaven | Online Mini Collectables | Collectables For Less Inc. | | Cambridge | Martas Replicas Co. | Cambridge Collectables Co. | | Frankfurt | Messner Shopping Network | Blauer See Auto, Co. | | Glendale | Gift Ideas Corp. | Boards & Toys Co. | | Lisboa | Porto Imports Co. | Lisboa Souveniers, Inc | | London | Stylish Desk Decors, Co. | Double Decker Gift Stores, Ltd | | Madrid | Corrida Auto Replicas, Ltd | Anton Designs, Ltd. | | Madrid | Corrida Auto Replicas, Ltd | ANG Resellers | | Madrid | CAF Imports | Anton Designs, Ltd. | | Madrid | CAF Imports | ANG Resellers | | Madrid | Euro+ Shopping Channel | Anton Designs, Ltd. | | Madrid | Euro+ Shopping Channel | ANG Resellers | | Madrid | Corrida Auto Replicas, Ltd | CAF Imports | | Madrid | Euro+ Shopping Channel | CAF Imports | | Madrid | Anton Designs, Ltd. | ANG Resellers | | Madrid | Euro+ Shopping Channel | Corrida Auto Replicas, Ltd | | Nantes | La Rochelle Gifts | Atelier graphique | | New Bedford | Mini Creations Ltd. | FunGiftIdeas.com | | New Haven | Super Scale Inc. | American Souvenirs Inc | | NYC | Muscle Machine Inc | Classic Legends Inc. | | NYC | Vitachrome Inc. | Land of Toys Inc. | | NYC | Vitachrome Inc. | Microscale Inc. | | NYC | Land of Toys Inc. | Classic Legends Inc. | | NYC | Muscle Machine Inc | Land of Toys Inc. | | NYC | Muscle Machine Inc | Microscale Inc. | | NYC | Vitachrome Inc. | Muscle Machine Inc | | NYC | Microscale Inc. | Classic Legends Inc. | | NYC | Vitachrome Inc. | Classic Legends Inc. | | NYC | Microscale Inc. | Land of Toys Inc. | | Paris | Lyon Souveniers | La Corne D'abondance, Co. | | Paris | Lyon Souveniers | Auto Canal+ Petit | | Paris | La Corne D'abondance, Co. | Auto Canal+ Petit | | Philadelphia | Motor Mint Distributors Inc. | Classic Gift Ideas, Inc | | San Francisco | Mini Wheels Co. | Corporate Gift Ideas Co. | | Singapore | Dragon Souveniers, Ltd. | Asian Shopping Network, Co | | Singapore | Handji Gifts& Co | Dragon Souveniers, Ltd. | | Singapore | Handji Gifts& Co | Asian Shopping Network, Co | +---------------+------------------------------+--------------------------------+ 43 rows in set ``` 我们通过以下连接条件连接了`customers`表： * 指定 `c1.city = c2.city` 以确保两个表的客户都是来自相同的城市。 * `c.customerName> c2.customerName`以确保不要得到相同的客户。 在本教程中，我们向您介绍了MySQL自连接，可以通过使用`INNER JOIN`或`LEFT JOIN`子句将一个表连接到自身。