MySQL高级讲座篇之：自连接（Self-Join）的应用：解决复杂关系查询的巧妙方法。

咳咳，大家好，老司机发车了！今天咱们聊点MySQL里比较有意思的——自连接。别一听“自连接”就觉得高深莫测，其实它就像自己跟自己玩，但玩得好，能解决不少复杂关系查询的问题。

一、 啥是自连接？（别装懂，再复习一遍）

说白了，自连接就是把一张表当成两张表来用，然后通过某种关联条件，把这两张“表”的数据连接起来。 为什么要这么干？因为有些时候，数据之间的关系就藏在同一张表里，不用自连接就搞不定。

举个例子，咱们有个员工表employees，字段包括员工ID（employee_id）、员工姓名（employee_name）、以及直属领导ID（manager_id）。注意，领导也是员工，只不过他/她的employee_id会出现在别人的manager_id里。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, '张三', NULL),
(2, '李四', 1),
(3, '王五', 1),
(4, '赵六', 2),
(5, '田七', 3);

现在，如果想查出所有员工以及他们的领导的名字，不用自连接，你会原地爆炸。

二、 自连接的基本语法

SELECT
    e1.employee_name AS employee_name,
    e2.employee_name AS manager_name
FROM
    employees e1
JOIN
    employees e2 ON e1.manager_id = e2.employee_id;

• employees e1 和 employees e2： 这就是把employees表起了两个别名，e1代表员工表，e2代表领导表。 别名很重要，不然你分不清哪个employee_name是员工的，哪个是领导的。
• ON e1.manager_id = e2.employee_id： 这是连接条件，意思是员工表（e1）的manager_id必须等于领导表（e2）的employee_id，这样才能把员工和对应的领导连起来。

运行上面的SQL，结果如下：

employee_name	manager_name
李四	张三
王五	张三
赵六	李四
田七	王五

可以看到，每个员工都找到了自己的领导。 张三没出现在employee_name列，因为他是老大，没有领导。

三、 自连接的应用场景

光知道语法没用，得知道啥时候用。 自连接的应用场景很多， 常见的有以下几种：

1. 层级关系查询： 就像上面的例子，员工和领导的关系，组织结构，部门层级等等。
2. 寻找相似数据： 比如，在商品表中，找到价格相近的商品。
3. 比较同一张表中的数据： 比如，找出所有比平均工资高的员工。
4. 递归关系查询： 比如，查找某个节点的所有子节点（需要结合其他方法，如递归函数或存储过程）。

下面咱们一个一个看。

3.1 层级关系查询进阶：找到所有下属

假设现在要找出某个领导的所有直接和间接下属。 这可就不是简单的一个自连接能搞定的了，需要配合递归查询（MySQL 8.0+支持）。

WITH RECURSIVE subordinate_tree AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE employee_id = 1  -- 假设要找张三的所有下属

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    JOIN subordinate_tree st ON e.manager_id = st.employee_id
)
SELECT * FROM subordinate_tree;

• WITH RECURSIVE subordinate_tree AS (...)： 定义一个递归公共表表达式（CTE），名为subordinate_tree。
• SELECT employee_id, employee_name, manager_id FROM employees WHERE employee_id = 1： 这是递归的起始条件，找到employee_id为1的员工（张三）。
• UNION ALL： 连接两个SELECT语句的结果。
• SELECT e.employee_id, e.employee_name, e.manager_id FROM employees e JOIN subordinate_tree st ON e.manager_id = st.employee_id： 这是递归的部分，在employees表中找到manager_id等于subordinate_tree中employee_id的员工，也就是张三的直接下属，然后把这些下属也加入到subordinate_tree中。
• 递归会一直进行下去，直到找不到新的下属为止。

运行结果：

employee_id	employee_name	manager_id
1	张三	NULL
2	李四	1
3	王五	1
4	赵六	2
5	田七	3

可以看到，张三的所有下属（包括直接和间接下属）都被找出来了。

3.2 寻找相似数据：价格相近的商品

假设我们有一个商品表products，字段包括商品ID（product_id）、商品名称（product_name）、商品价格（price）。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, '苹果', 5.00),
(2, '香蕉', 6.00),
(3, '橘子', 5.50),
(4, '梨子', 7.00),
(5, '西瓜', 15.00);

现在要找出所有价格相差不超过1元的商品组合。

SELECT
    p1.product_name AS product1,
    p2.product_name AS product2,
    ABS(p1.price - p2.price) AS price_difference
FROM
    products p1
JOIN
    products p2 ON p1.product_id <> p2.product_id  -- 排除自己和自己比较
WHERE
    ABS(p1.price - p2.price) <= 1
ORDER BY
    price_difference;

• p1.product_id <> p2.product_id： 这个条件很重要，不然每个商品都会和自己比较一次，结果就没意义了。
• ABS(p1.price - p2.price) <= 1： 计算两个商品价格的绝对值差，并筛选出差值小于等于1的组合。

运行结果：

product1	product2	price_difference
苹果	橘子	0.50
橘子	苹果	0.50
香蕉	橘子	0.50
橘子	香蕉	0.50

可以看到，苹果和橘子，香蕉和橘子的价格比较接近。

3.3 比较同一张表中的数据：找出比平均工资高的员工

还是用上面的employees表，假设现在要找出所有工资高于平均工资的员工。 首先，我们需要计算平均工资，然后才能和每个员工的工资进行比较。

SELECT
    e.employee_name,
    e.salary
FROM
    employees e
JOIN (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_salaries
ON e.salary > avg_salaries.avg_salary;

• (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_salaries： 这是一个子查询，用来计算平均工资，并把结果命名为avg_salaries。
• ON e.salary > avg_salaries.avg_salary： 把每个员工的工资和平均工资进行比较。

当然，更简单的写法是用WHERE子句和子查询：

SELECT
    employee_name,
    salary
FROM
    employees
WHERE
    salary > (SELECT AVG(salary) FROM employees);

这两种写法效果一样，选择哪种看个人喜好。

3.4 递归关系查询：查找某个节点的所有子节点

这个例子和3.1很像，只不过场景不同。 假设我们有一个分类表categories，字段包括分类ID（category_id）、分类名称（category_name）、父分类ID（parent_id）。

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50),
    parent_id INT
);

INSERT INTO categories (category_id, category_name, parent_id) VALUES
(1, '电子产品', NULL),
(2, '电脑', 1),
(3, '手机', 1),
(4, '笔记本电脑', 2),
(5, '平板电脑', 2),
(6, '智能手机', 3);

现在要找出所有category_id为2（电脑）的子分类。

WITH RECURSIVE subcategories AS (
    SELECT category_id, category_name, parent_id
    FROM categories
    WHERE category_id = 2

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_id
    FROM categories c
    JOIN subcategories sc ON c.parent_id = sc.category_id
)
SELECT * FROM subcategories;

运行结果：

category_id	category_name	parent_id
2	电脑	NULL
4	笔记本电脑	2
5	平板电脑	2

可以看到，电脑的所有子分类（笔记本电脑和平板电脑）都被找出来了。

四、 自连接的注意事项

自连接虽然强大，但用不好也容易出问题，需要注意以下几点：

1. 别名一定要起好： 不然分不清哪个字段是哪个表的，SQL就没法写了。
2. 连接条件要明确： 不然容易产生笛卡尔积，导致查询结果数量爆炸。
3. 注意性能问题： 自连接本质上是把一张表当成多张表来用，会增加查询的复杂度，影响性能。 如果数据量很大，要考虑优化SQL，比如加索引。
4. 避免无限递归： 在使用递归查询的时候，一定要确保数据没有循环引用，不然会陷入无限循环。

五、 自连接 vs 子查询

很多人可能会问，有些场景用自连接可以解决，用子查询也可以解决，那到底该用哪个？

一般来说，如果查询涉及到同一张表的多个维度，或者需要比较同一张表中的数据，自连接更适合。 如果查询比较简单，只需要从一张表中筛选数据，子查询可能更清晰。

选择哪个，没有绝对的标准，要根据具体情况来判断。 可以都尝试一下，看看哪个性能更好，可读性更强。

六、 总结

自连接是MySQL里一个很有用的技巧，可以解决很多复杂的关系查询问题。 掌握自连接，可以让你在SQL的世界里更加游刃有余。 但是，也要注意自连接的性能问题，避免滥用。

好了，今天的自连接讲座就到这里。 希望大家以后遇到类似的问题，能想起老司机今天讲的内容，灵活运用自连接，写出高效优雅的SQL。 下课！