5.4 自连接
在本教程中，您将了解如何使用连接语句将表连接到表自身，即，在同一张表上自己连接自己。
在之前的教程中，已经学习了如何使用INNER JOIN，LEFT JOIN 或 CROSS JOIN子句将表连接到其他表。 但是，有一个特殊情况，需要将表自身连接，这被称为自连接。
当您想将表中行与同一表中的其他行组合时，可以使用自连接。要执行自联接操作必须使用表别名来帮助MySQL在单个查询中区分左表与同一张表的右表。
MySQL自连接的例子
我们来看看示例数据库(yiibaidb)中的employees表，其表结构如下所示 -
要获得整个组织结构，可以使用employeeNumber和reportsTo列将employees表连接自身。employees表有两个角色：一个是经理，另一个是直接报告者(即，下属员工)。
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;执行上面查询，得到以下结果 -
+--------------------+--------------------+
| 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函数来显示总经理。
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;执行上面查询，得到以下结果 -
+--------------------+--------------------+
| 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表连接自身来显示位于同一个城市的客户列表。参考以下查询语句 -
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;执行上面查询语句，得到以下结果 -
+---------------+------------------------------+--------------------------------+
| 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子句将一个表连接到自身。
Last updated