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 保存上级员工的主键。使用左连接可以保留根节点;如果改成内连接,没有上级的“陈总”会被过滤掉。

适用场景与边界

自连接适合查询直属关系、同表配对、前后版本或相邻记录。它一次通常只展开一层关系。如果要查询任意深度的组织树,继续叠加多次自连接会迅速变复杂,应改用递归公共表表达式。

设计层级表时,还要防止记录引用自身或形成循环。外键只能保证目标记录存在,不能自动阻止完整的循环链,业务写入时仍需校验。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用自连接

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通