九月 1, 2023
摘要：在本教程中，您将学习如何使用 PostgreSQL 自连接技术来比较同一表中的行。
目录
PostgreSQL 自连接简介
自连接是一种将表与其自身连接的常规连接。在实践中，您通常使用自连接来查询分层数据或比较同一表中的行。
要形成自连接，请使用不同的表别名指定同一表两次，并在ON关键字后提供连接谓词。
以下查询使用INNER JOIN将表连接到自身：
SELECT select_list
FROM table_name t1
INNER JOIN table_name t2 ON join_predicate;
在此语法中，使用INNER JOIN子句将table_name与其自身连接起来。
另外，您可以使用LEFT JOIN或RIGHT JOIN子句将表连接到自身，如下所示：
SELECT select_list
FROM table_name t1
LEFT JOIN table_name t2 ON join_predicate;
PostgreSQL 自连接示例
让我们举一些使用自连接的例子。
1) 查询分层数据示例
让我们设置一个示例表来进行演示。
假设，您有以下组织结构：
以下语句创建employee表并向表中插入一些示例数据。
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE
);
INSERT INTO employee (
employee_id,
first_name,
last_name,
manager_id
)
VALUES
(1, 'Windy', 'Hays', NULL),
(2, 'Ava', 'Christensen', 1),
(3, 'Hassan', 'Conner', 1),
(4, 'Anna', 'Reeves', 2),
(5, 'Sau', 'Norman', 2),
(6, 'Kelsie', 'Hays', 3),
(7, 'Tory', 'Goff', 3),
(8, 'Salley', 'Lester', 3);
在此employee表中，manager_id列引用employee_id列。manager_id列中的值显示员工直接向其汇报的经理。当manager_id列中的值为空时，该员工不向任何人报告。换句话说，他或她是最高管理者。
以下查询使用自连接来查找谁向谁报告：
SELECT
e.first_name || ' ' || e.last_name employee,
m .first_name || ' ' || m .last_name manager
FROM
employee e
INNER JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;
此查询两次引用employees表，一次作为员工表，另一次作为经理表。它使用表别名e标识员工表和表别名m标识经理表。
连接谓词通过匹配employee_id和manager_id列中的值来查找员工/经理对。
请注意，最高管理者不会出现在输出中。
要将最高管理者包含在结果集中，请使用LEFT JOIN替代INNER JOIN子句，如以下查询所示：
SELECT
e.first_name || ' ' || e.last_name employee,
m .first_name || ' ' || m .last_name manager
FROM
employee e
LEFT JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;
2) 比较同一个表的行
请查看 DVD 租赁数据库中的film表，如下：
以下查询查找所有具有相同长度的电影对，
SELECT
f1.title,
f2.title,
f1.length
FROM
film f1
INNER JOIN film f2
ON f1.film_id <> f2.film_id AND
f1.length = f2.length;
连接谓词匹配具有相同长度 (f1.length = f2.length) 的两个不同电影 (f1.film_id <> f2.film_id)。
概括
- PostgreSQL 自连接是一种常规连接，它使用
INNER JOIN或LEFT JOIN将表与其自身连接。 - 自连接对于查询分层数据或比较同一表中的行非常有用。