自连接

1. 概述

在 SQL 中，自连接（Self Join）是一种特殊的表连接方式，用于将同一个表的不同行连接在一起。自连接通常用于查询表中具有某种关系的行，例如员工表中查找某个员工的上级或下级。

2. 理论解释

2.1 什么是自连接？

自连接是指在一个表中，将表本身作为两个不同的表来处理，并通过某种条件将它们连接起来。这种连接方式可以帮助我们查询表中具有某种关系的行。

2.2 自连接的应用场景

自连接常用于以下场景：

• 层次结构查询：例如，员工表中查找某个员工的上级或下级。
• 时间序列分析：例如，查找某个时间段内的连续订单。
• 比较同一表中的不同行：例如，查找同一表中价格高于平均价格的商品。

3. 代码示例

3.1 示例表结构

假设我们有一个员工表 employees，表结构如下：

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

表中的 manager_id 字段指向该员工的上级（即另一个员工的 employee_id）。

3.2 插入示例数据

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2);

3.3 自连接查询

3.3.1 查找所有员工及其上级

SELECT 
    e1.employee_name AS employee,
    e2.employee_name AS manager
FROM 
    employees e1
LEFT JOIN 
    employees e2 ON e1.manager_id = e2.employee_id;

解释：

• e1 和 e2 是 employees 表的两个别名。
• LEFT JOIN 用于连接 e1 和 e2，条件是 e1.manager_id = e2.employee_id。
• 结果将显示每个员工的姓名及其上级的姓名。

3.3.2 查找所有没有上级的员工

SELECT 
    e1.employee_name AS employee
FROM 
    employees e1
LEFT JOIN 
    employees e2 ON e1.manager_id = e2.employee_id
WHERE 
    e2.employee_id IS NULL;

解释：

• 通过 LEFT JOIN 连接 e1 和 e2，条件是 e1.manager_id = e2.employee_id。
• WHERE e2.employee_id IS NULL 用于筛选出没有上级的员工。

4. 实践练习

4.1 练习 1：查找所有员工及其下级

编写一个 SQL 查询，查找所有员工及其直接下级。

4.2 练习 2：查找所有员工及其所有下级

编写一个 SQL 查询，查找所有员工及其所有下级（包括间接下级）。提示：可以使用递归查询。

4.3 练习 3：查找所有员工及其上级和下级

编写一个 SQL 查询，查找所有员工及其上级和下级。

5. 总结

自连接是 SQL 中一种强大的查询技术，适用于查询表中具有某种关系的行。通过自连接，我们可以轻松地处理层次结构、时间序列分析等复杂查询。掌握自连接的使用，将帮助你更好地理解和应用 SQL 查询。

6. 进一步学习

• 递归查询：学习如何使用递归查询处理层次结构数据。
• 公共表表达式 (CTE)：了解如何使用 CTE 简化复杂查询。
• 窗口函数：学习如何使用窗口函数进行更高级的数据分析。

通过不断练习和实践，你将能够熟练掌握自连接技术，并在实际项目中灵活应用。