mysql自连接_MySQL自连接

在本教程中，您将了解如何使用连接语句将表连接到表自身，即，在同一张表上自己连接自己。

在之前的教程中，已经学习了如何使用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子句将一个表连接到自身。

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。