如何用SQL SELF JOIN查询组织架构-SQL-PHP中文网

self join本质是同一张表起两个别名进行连接；需用left join查直属上级以保留ceo，manager_id字段必须建索引，递归查询需with recursive且注意终止条件。

如何用sql self join查询组织架构

SELF JOIN 本质是把一张表当两张用

SELF JOIN 不是特殊语法，只是在 FROM 子句里对同一张表起两个别名，然后像连接两张表一样写 ON 条件。组织架构表通常有 id 和 manager_id 字段，靠这个关联就能查出上下级关系。

常见错误是漏写别名或混淆字段归属——比如写 SELECT name, manager_id 却没说明是哪个实例的 name，数据库会报错“ambiguous column”。必须显式指定 emp.name、mgr.name 这类带别名的引用。

查直属上级：ON emp.manager_id = mgr.id

这是最常用场景。假设表叫 employees，字段为 id、name、manager_id（可能为 NULL）：

SELECT 
  emp.name AS employee,
  mgr.name AS manager
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

注意点：

用 LEFT JOIN 而不是 INNER JOIN，否则 CEO（manager_id IS NULL）会被过滤掉
mgr.id 是上级的主键，emp.manager_id 是下级记录里存的上级 ID，方向不能反
如果 manager_id 允许为字符串（比如工号），确保类型和 mgr.id 一致，否则隐式转换可能导致索引失效

查所有下属（递归需求）不能只靠单次 SELF JOIN

单次 JOIN 只能查一层下属（比如经理→员工），查“部门所有子员工”需要递归。MySQL 8.0+、PostgreSQL、SQL Server 支持 WITH RECURSIVE，但旧版 MySQL 或 SQLite 就得靠应用层循环或临时表模拟。

以 PostgreSQL 为例，查 ID=100 的管理者及其全部间接下属：

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE id = 100
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.level + 1
  FROM employees e
  INNER JOIN org ON e.manager_id = org.id
)
SELECT * FROM org ORDER BY level;

容易被忽略的是终止条件：递归部分的 INNER JOIN 必须严格匹配 e.manager_id = org.id，且初始查询不能漏掉根节点本身（否则结果为空）。