# 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`子句将一个表连接到自身。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-4/zi-lian-jie.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.