PostgreSQL / 系统课时
使用自连接
使用自连接
本课解决“同一张表中的记录如何相互关联”的问题。你将通过员工与直属上级的层级结构,掌握为同一张表设置多个别名并完成自连接查询。
学习目标
- 理解自连接适用的表结构
- 使用不同别名区分同一张表的角色
- 正确保留没有上级的根节点
自连接的本质
自连接不是新的连接类型,而是让同一张表在一次查询中扮演多个角色。例如员工表中的 manager_id 仍然引用员工表的 id,一行代表下属,另一行代表上级。
由于两侧来自同一张表,必须使用别名区分字段。常见写法是把下属命名为 e,把上级命名为 m。
完整查询示例
下面使用 LEFT JOIN 查询全部员工及其直属上级。总经理没有上级,因此上级姓名返回 NULL。
sql
CREATE TEMP TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT REFERENCES employee(id)
);
INSERT INTO employee (id, name, manager_id) VALUES
(1, '陈总', NULL),
(2, '李经理', 1),
(3, '王工程师', 2),
(4, '赵工程师', 2);
SELECT
e.id,
e.name AS employee_name,
m.name AS manager_name
FROM employee AS e
LEFT JOIN employee AS m
ON m.id = e.manager_id
ORDER BY e.id;预期输出
text
id | employee_name | manager_name
----+---------------+--------------
1 | 陈总 |
2 | 李经理 | 陈总
3 | 王工程师 | 李经理
4 | 赵工程师 | 李经理
(4 rows)e.manager_id 保存上级员工的主键。使用左连接可以保留根节点;如果改成内连接,没有上级的“陈总”会被过滤掉。
适用场景与边界
自连接适合查询直属关系、同表配对、前后版本或相邻记录。它一次通常只展开一层关系。如果要查询任意深度的组织树,继续叠加多次自连接会迅速变复杂,应改用递归公共表表达式。
设计层级表时,还要防止记录引用自身或形成循环。外键只能保证目标记录存在,不能自动阻止完整的循环链,业务写入时仍需校验。
按自己的节奏,确认真正理解。