MySQL SELF JOIN

---

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

---

在之前的教程中，你已经学会了如何用INNER JOIN，   LEFT JOIN或CROSS 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子句将表连接到自身。

---