31 自连接

查询同一张表中的关系，并跟踪员工层级。

当一张表与自身连接时，就是自连接。你需要为它取两个别名，让 PostgreSQL 能分辨你指的是哪一个副本，并用一个条件把相关行连接起来。只要数据模型表示同类行之间的关系，就会用到自连接——员工与经理、评论与父评论、前驱与后继。对于任意深度的层级关系，合适的工具是递归 CTE（后面的课程会介绍）；只有一层时，自连接简单而且快速。

本章要点

使用两个别名把一张表与自身连接
为自连接选择 INNER 还是 LEFT
建模一层层级关系（员工 → 经理）
初步了解任意深度的递归 CTE
为列取名以提高清晰度

示例

示例 31.1 自连接

DROP TABLE IF EXISTS employees_example;
CREATE TABLE employees_example (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  manager_id integer REFERENCES employees_example(id)
);

INSERT INTO employees_example VALUES
  (1, 'Alice',  NULL),    -- top of the hierarchy
  (2, 'Bob',    1),       -- reports to Alice
  (3, 'Carol',  1),       -- reports to Alice
  (4, 'Dave',   2),       -- reports to Bob
  (5, 'Eve',    2);       -- reports to Bob

-- One level: employee and their direct manager
SELECT e.name AS employee, m.name AS manager
FROM employees_example e
LEFT JOIN employees_example m ON e.manager_id = m.id
ORDER BY e.id;

-- Count direct reports per manager
SELECT m.name AS manager, count(e.id) AS direct_reports
FROM employees_example m
LEFT JOIN employees_example e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY m.id;

-- A taste of recursion: full chain to the top (covered fully in the CTE lesson)
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE id = 4                                       -- start at Dave
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees_example e
  JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

在连接 FROM employees_example e LEFT JOIN employees_example m ON e.manager_id = m.id 中，e 是员工行，m 是经理行。两者都指向同一张物理表——PostgreSQL 把这些别名当作彼此独立的副本。使用 LEFT JOIN 意味着没有经理的员工（Alice）仍会出现，经理列为 NULL。

第二个查询中的 count(e.id)（而不是 count(*)）是标准写法：count(*) 会把未匹配行产生的 NULL 也算进去，错误地让每个叶子经理至少拥有 1 个直接下属。count(e.id) 只统计非 NULL 的 id，即实际的下属。

结尾的递归 CTE 是对 CTE 课程的预览。WITH RECURSIVE 让你可以遍历任意深度——锚点行（Dave）是起点；递归步骤重新连接到 chain，每次向上走一层。

运行示例

运行方式：

$ psql -f source/self-join.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 5
 employee | manager
----------+---------
 Alice    |
 Bob      | Alice
 Carol    | Alice
 Dave     | Bob
 Eve      | Bob
(5 rows)
 ...

常见陷阱

用 INNER JOIN 而不是 LEFT JOIN，会悄悄丢掉层级顶端的行。顶层行没有经理，INNER JOIN 会将其丢弃。
忘记 e 和 m 是同一张表。若条件可能允许同一行匹配，可以使用 WHERE e.id <> m.id 排除这种情况。
想找出“没有子项的组”时，不要统计 *，而要统计连接进来的表的 id——count(child.id) 才是正确写法。
对于任意深度，反复进行自连接（emp → mgr → mgr's mgr → ...）无法扩展。请改用 WITH RECURSIVE。

提示

对称关系（friend-of）通常使用单独的关联表，而不是自连接。非对称关系（manager-of、parent-of）很适合用自引用列表示。

动手练习

在 Carol 下面新增第六名员工。重新运行直接下属查询——现在 Carol 应该有一名直接下属。然后修改递归 CTE，让它从 Eve 而不是 Dave 开始，看看链条如何变化。

31 自连接

本章要点

示例

运行示例

常见陷阱

下载