SQLite Self-join

SQLite Self-join 自连接

自连接是一种特殊的连接，它允许您使用 LEFT JOIN 或 INNER JOIN 子句将一个表连接到自身。您可以使用自连接创建一个结果集，将行与同一表中的其他行连接起来。

因为您不能在查询中多次引用同一张表，所以在使用自连接时需要使用表别名为该表分配不同的名称。

自连接比较同一张表中相同或不同列的值。自连接仅涉及一个表。

您经常使用自连接来查询表中存储的父/子关系或获取累计总数。

自连接的案例

我们将使用示例数据库中的员工表进行演示。

员工表不仅存储员工数据，还存储组织数据。ReportsTo 列指定员工之间的报告关系。

如果员工向经理汇报，则员工所在行的 ReportsTo 列的值等于经理所在行的 EmployeeId 列的值。如果员工不向任何人汇报，则 ReportsTo 列为 NULL。

要获取谁是谁的直接下属的信息，可以使用以下语句：

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;

该语句使用 INNER JOIN 子句将员工与其自身连接起来。员工表有两个角色：员工和经理。

因为我们使用 INNER JOIN 子句将员工表与其自身连接，所以结果集不包含经理列包含 NULL 值的行。

请注意，连接运算符 || 将多个字符串连接成一个字符串。在示例中，我们使用连接运算符通过连接名字、空格和姓氏来生成员工的全名。

如果您想查询不向任何人报告的首席执行官，则需要将上面查询中的 INNER JOIN 子句更改为 LEFT JOIN 子句。

安德鲁·亚当斯 (Andrew Adams) 是首席执行官，因为他没有举报任何人。

您可以使用自连接技术来查找位于同一城市的员工，如以下查询：

SELECT DISTINCT
	e1.city,
	e1.firstName || ' ' || e1.lastname AS fullname
FROM
	employees e1
INNER JOIN employees e2 ON e2.city = e1.city 
   AND (e1.firstname <> e2.firstname AND e1.lastname <> e2.lastname)
ORDER BY
	e1.city;

连接条件有两种表达式：

• e1.city = e2.city 确保两个员工位于同一城市

• e.firstname <> e2.firstname AND e1.lastname <> e2.lastname 确保 e1 和 e2 不是同一个员工，假设没有员工具有相同的名字和姓氏。