mysql self join_MySQL SELF JOIN

MySQL SELF JOIN

简介：在本教程中，您将学习如何使用连接语句将表连接到自身的MySQL自连接。

在之前的教程中，你已经学会了如何用LEFT JOIN或

如果要将行与同一表中的其他行组合，可以使用自联接。要执行自联接操作，必须使用表别名来帮助MySQL在单个查询中区分左表和同一表的右表。

MySQL 自联接实例

我们来看看示例数据库中的employees表。

在employees表中，我们不仅存储员工数据，还存储组织结构数据。reportsto列用于确定员工的经理ID。

+----------------+

| employees |

+----------------+

| employeeNumber |

| lastName |

| firstName |

| extension |

| email |

| officeCode |

| reportsTo |

| jobTitle |

+----------------+

8 rows in set (0.01 sec)

要获取整个组织结构，可以使用employees表中的employeeNumber和reportsTo列将表连接到自身。employees表有两个角色：一个是Manager，另一个是Direct Reports。

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 | Bott, Larry |

| Bondur, Gerard | Gerard, Martin |

| Bondur, Gerard | Hernandez, Gerard |

| Bondur, Gerard | Jones, Barry |

| Bondur, Gerard | Castillo, Pamela |

| Bondur, Gerard | Bondur, Loui |

| Bow, Anthony | Firrelli, Julie |

...

在上面的输出中，您只看到拥有经理的员工。但是，您没有看到顶级经理，因为他的名字由于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 | King, Tom |

| Patterson, William | Fixter, Andy |

| Patterson, William | Marsh, Peter |

| Patterson, Mary | Bow, Anthony |

| Patterson, Mary | Patterson, William |

| Patterson, Mary | Bondur, Gerard |

| Patterson, Mary | Nishi, Mami |

...

通过使用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 | Online Mini Collectables | Collectables For Less Inc. |

| Brickhaven | Collectables For Less Inc. | Auto-Moto Classics Inc. |

| Brickhaven | Online Mini Collectables | Auto-Moto Classics Inc. |

| Cambridge | Marta's Replicas Co. | Cambridge Collectables Co. |

...

customers通过以下连接条件加入了表：

c1.city = c2.city 确保两个客户都拥有相同的城市。

c.customerName > c2.customerName 确保我们不会得到同一个客户。

在本教程中，我们向您介绍了MySQL自联接，它允许您通过使用INNER JOIN或LEFT JOIN子句将表连接到自身。