Skip to main content

MySQL自连接-将表连接到自身

在前面的教程中,您已经学习了如何使用内部联接、左联接、右联接或交叉联接子句将一个表联接到其他表。然而,有一种特殊情况是,需要将一个表连接到它自己,这被称为自连接。

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

要执行自联接,必须使用表别名,以避免在一个查询中重复同一个表名两次。请注意,在查询中两次或多次引用表而不使用表别名会导致错误。

MySQL自连接示例

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

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

1) 使用内部连接子句的MySQL自连接

要获得整个组织结构,可以使用employeeNumber和reportsTo列将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;

MySQL Selft Join Example

输出仅显示有经理的员工。然而,你看不到总统,因为他的名字由于内部连接条款而被过滤掉了。

2) 使用LEFT-join子句的MySQL自连接

总裁是指报告中没有任何经理或价值观的员工。报告为空。

下面的语句使用LEFT JOIN子句而不是internal 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;

MySQL Self Join with LEFT JOIN technique

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;

MySQL Self Join cutomers located in the same city

在本例中,表customers使用以下连接条件连接到自身:

在本教程中,您学习了如何使用内部连接或左连接子句将表连接到自身。