MySQL Self Join

摘要：在本教程中，您将学习如何使用MySQL 自连接，即使用内连接或左连接将表连接到自身。

在前面的教程中，您学习了如何使用INNER JOIN 、 LEFT JOIN 、 RIGHT JOIN或CROSS JOIN子句将表连接到其他表。但是，有一种特殊情况，您需要将表连接到自身，这称为自连接。

自连接通常用于查询分层数据或将一行与同一表中的其他行进行比较。

要执行自联接，必须使用表别名，以免在单个查询中重复相同的表名称两次。请注意，在查询中引用表两次或多次而不使用表别名将导致错误。

MySQL 自连接示例

让我们看一下示例数据库中的employees表。

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

1）MySQL使用INNER JOIN子句进行自连接

要获取整个组织结构，您可以使用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;Code language: SQL (Structured Query Language) (sql)

试试看

输出仅显示拥有经理的员工。但是，您看不到总统，因为他的名字因INNER JOIN子句而被过滤掉。

2）MySQL使用LEFT JOIN子句的自连接

总裁是没有任何经理的员工，或者reportsTo列中的值为NULL 。

以下语句使用LEFT JOIN子句而不是INNER JOIN来包含主席：

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;Code language: SQL (Structured Query Language) (sql)

试试看

3）使用MySQL自连接来比较连续的行

通过使用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;Code language: SQL (Structured Query Language) (sql)

试试看

在此示例中，表customers使用以下连接条件连接到自身：

• c1.city = c2.city确保两个客户拥有相同的城市。
• c.customerName > c2.customerName确保不包含相同的客户。

在本教程中，您学习了如何进行 MySQL 自连接，即使用INNER JOIN或LEFT JOIN子句将表连接到自身。