各位观众老爷，大家好！今天咱们来聊聊MySQL里一个有点“人格分裂”但又非常实用的技巧——自连接(Self-Join)。 啥是自连接？说白了，就是一张表自己跟自己玩，自己跟自己关联。听起来有点绕，但当你需要处理层级数据结构，比如组织架构、商品分类等等，它就派上大用场了。
一、 啥时候需要自连接？
咱们先举个例子，更容易理解。假设咱们有个employee表，用来记录员工信息，其中有个manager_id字段，指向的是该员工的直接领导的employee_id。
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
manager_id INT,
department VARCHAR(255),
salary DECIMAL(10, 2)
);
INSERT INTO employee (employee_id, employee_name, manager_id, department, salary) VALUES
(1, '张三', NULL, '技术部', 10000.00),
(2, '李四', 1, '技术部', 8000.00),
(3, '王五', 1, '技术部', 7000.00),
(4, '赵六', 2, '技术部', 6000.00),
(5, '小明', NULL, '销售部', 9000.00),
(6, '小红', 5, '销售部', 7500.00),
(7, '小刚', 5, '销售部', 6500.00);这个表的数据结构，就是典型的层级结构。张三是李四和王五的领导，李四又是赵六的领导，以此类推。
现在，如果我想知道每个员工的名字和他们领导的名字，咋办？这就需要自连接了。
二、 自连接的语法
自连接的本质就是把一张表当成两张表来用，然后通过一个共同的字段进行关联。 所以我们需要给同一张表取两个别名。
基本语法如下：
SELECT
e.employee_name, -- 员工的名字
m.employee_name AS manager_name -- 领导的名字
FROM
employee e -- 给 employee 表取别名 e (employee)
JOIN
employee m ON e.manager_id = m.employee_id -- 给 employee 表取别名 m (manager)，并关联员工的 manager_id 和领导的 employee_id
WHERE
e.manager_id IS NOT NULL; -- 排除那些没有领导的人 (老板级别)解释一下：
employee e: 把employee表看作是员工表，别名是e。employee m: 把employee表看作是领导表，别名是m。ON e.manager_id = m.employee_id: 这是连接条件，意思是员工的manager_id等于领导的employee_id，这样就把员工和他们的领导关联起来了。WHERE e.manager_id IS NOT NULL: 这个条件排除了最高领导，也就是manager_id为空的那些人。
运行上面的SQL，结果如下：
| employee_name | manager_name |
|---|---|
| 李四 | 张三 |
| 王五 | 张三 |
| 赵六 | 李四 |
| 小红 | 小明 |
| 小刚 | 小明 |
看到了吧，每个员工和他们的领导都配对成功了！
三、 自连接的类型
自连接本质上还是连接查询，所以也分不同的类型，比如INNER JOIN、LEFT JOIN、RIGHT JOIN等。 上面的例子用的是INNER JOIN，只会返回那些有领导的员工。 如果我想列出所有员工，包括那些没有领导的（比如老板），可以用LEFT JOIN。
SELECT
e.employee_name,
COALESCE(m.employee_name, '顶头上司') AS manager_name -- 如果没有领导，显示'顶头上司'
FROM
employee e
LEFT JOIN
employee m ON e.manager_id = m.employee_id;这里用LEFT JOIN，保证了employee表（左表）的所有记录都会被返回。如果某个员工没有领导（manager_id为空），那么m.employee_name就会是NULL，COALESCE函数会把它替换成’顶头上司’。 运行后你会发现，张三和小明的领导变成了“顶头上司”。
四、 自连接的进阶应用
光知道怎么查领导还不够，咱们再来点更高级的。
1. 查找所有员工及其上级的部门
SELECT
e.employee_name,
m.department AS manager_department
FROM
employee e
JOIN
employee m ON e.manager_id = m.employee_id;这个SQL可以查出每个员工和他们领导所在的部门。
2. 查找所有直接下属超过2人的经理
这个稍微复杂一点，需要用到GROUP BY和HAVING。
SELECT
m.employee_name AS manager_name,
COUNT(e.employee_id) AS subordinate_count
FROM
employee e
JOIN
employee m ON e.manager_id = m.employee_id
GROUP BY
m.employee_id
HAVING
COUNT(e.employee_id) > 2;解释一下：
GROUP BY m.employee_id: 按照领导的employee_id分组，统计每个领导有多少下属。HAVING COUNT(e.employee_id) > 2: 筛选出下属数量大于2的领导。
3. 查找所有部门的平均工资高于其领导的部门
这个就更复杂了，需要用到子查询。
SELECT DISTINCT
e.department
FROM
employee e
JOIN
employee m ON e.manager_id = m.employee_id
WHERE
e.salary > (SELECT AVG(e2.salary) FROM employee e2 WHERE e2.department = m.department);这个SQL会找到平均工资高于其领导部门平均工资的所有部门。
4. 查找领导层级
比如，想知道某个员工的所有上级领导，直到顶头上司。这个就得用到递归查询了，MySQL 8.0 之后才支持。我们假设要查询员工ID为4（赵六）的所有领导。
WITH RECURSIVE employee_hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
1 AS level -- 初始层级为1
FROM
employee
WHERE
employee_id = 4 -- 从ID为4的员工开始
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1 -- 层级递增
FROM
employee e
JOIN
employee_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT
employee_id,
employee_name,
manager_id,
level
FROM
employee_hierarchy;这个SQL会递归地查找赵六的所有上级，level字段表示层级。
五、 其他层级数据结构的应用
除了员工信息，自连接还可以在其他层级数据结构中发挥作用。比如：
- 商品分类: 一个商品分类可以有父分类，通过自连接可以查找某个分类的所有子分类，或者所有父分类。
- 评论回复: 一条评论可以回复另一条评论，通过自连接可以查找某个评论的所有回复，或者某个评论的所有父评论。
- 组织机构: 一个部门可以有父部门，通过自连接可以查找某个部门的所有子部门，或者所有父部门。
六、 自连接的注意事项
- 性能: 自连接可能会导致性能问题，特别是当表的数据量很大时。因为相当于两张表做笛卡尔积，然后再筛选。所以要尽量避免全表扫描，合理使用索引。
- 别名: 一定要给表取别名，否则会引起歧义。
- 连接条件: 连接条件要写对，否则会得到错误的结果。
- 递归深度: 在使用递归查询时，要注意递归深度，避免无限循环。 可以设置
max_execution_time来限制查询时间。
七、 小结
自连接是一种强大的SQL技巧，可以用来处理层级数据结构。虽然有点绕，但掌握了它，你就可以轻松解决很多复杂的问题。 关键在于理解其本质： 将同一张表视为两张表，通过关联字段连接起来。 灵活运用 INNER JOIN, LEFT JOIN 以及 MySQL 8.0 引入的 WITH RECURSIVE 可以实现各种复杂的层级查询。
记住，熟能生巧，多练习才能真正掌握自连接的精髓。 好了，今天的讲座就到这里，大家慢慢消化，有问题随时提问！