MySQL高级讲座篇之：MySQL 8.0的`SET`子句中的`CTE`：如何实现更复杂的更新和删除操作？

各位观众老爷们，大家好！今天咱们聊点硬核的，关于MySQL 8.0的Common Table Expression (CTE)在SET子句中的妙用。别怕，听起来高大上，其实理解了它，你的SQL功力能瞬间提升几个level。

开场白：SQL界的变形金刚

SQL是个好东西，但有时候写起来就像搭积木，尤其是遇到复杂的更新和删除逻辑，一层套一层的子查询，看得人头皮发麻。这时候，CTE就像SQL界的变形金刚，能把复杂的问题拆解成一个个清晰的模块，让你思路更清晰，代码也更易维护。

什么是CTE？（温故而知新）

CTE，全称Common Table Expression，中文名叫“通用表表达式”，你可以把它想象成一个临时表，只在当前查询中有效。它用WITH关键字定义，后面跟着CTE的名字，以及它的查询逻辑。

基本语法：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

SET子句中的CTE：更新的艺术

在MySQL 8.0之前，要在UPDATE语句中使用复杂的逻辑，往往需要借助子查询或者临时表。但是，有了CTE，我们可以直接在SET子句中引用它，让更新操作更加简洁高效。

举个栗子：假设我们有一个employees表，记录了员工的信息，包括id、name、salary和department_id。还有一个departments表，记录了部门的信息，包括id和budget。现在，我们需要把每个部门的最高工资员工的工资提高10%，但同时要保证提高后的工资不超过该部门的预算。

没有CTE的时候，你可能会这么写：

UPDATE employees
SET salary = CASE
    WHEN salary * 1.1 > (SELECT budget FROM departments WHERE id = employees.department_id) THEN (SELECT budget FROM departments WHERE id = employees.department_id)
    ELSE salary * 1.1
END
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, department_id, salary,
               ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
        FROM employees
    ) AS subquery
    WHERE rn = 1
);

这段代码看起来是不是很吓人？子查询嵌套了好几层，可读性极差。而且，MySQL优化器可能也hold不住，性能堪忧。

现在，让我们用CTE来改造一下：

WITH HighestSalaryEmployees AS (
    SELECT
        e.id AS employee_id,
        e.department_id,
        e.salary,
        d.budget
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.id
    WHERE
        e.id IN (
            SELECT id
            FROM (
                SELECT id, department_id, salary,
                       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
                FROM employees
            ) AS subquery
            WHERE rn = 1
        )
)
UPDATE employees
SET salary = CASE
    WHEN salary * 1.1 > (SELECT budget FROM HighestSalaryEmployees WHERE employee_id = employees.id) THEN (SELECT budget FROM HighestSalaryEmployees WHERE employee_id = employees.id)
    ELSE salary * 1.1
END
WHERE id IN (SELECT employee_id FROM HighestSalaryEmployees);

虽然还是有点长，但是结构清晰多了。我们首先定义了一个CTE HighestSalaryEmployees，它包含了每个部门最高工资员工的id、department_id、salary和部门的budget。然后在UPDATE语句中，我们可以直接引用这个CTE，计算新的工资。

我们可以继续优化，把CASE语句中的子查询也去掉：

WITH HighestSalaryEmployees AS (
    SELECT
        e.id AS employee_id,
        e.department_id,
        e.salary,
        d.budget
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.id
    WHERE
        e.id IN (
            SELECT id
            FROM (
                SELECT id, department_id, salary,
                       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
                FROM employees
            ) AS subquery
            WHERE rn = 1
        )
)
UPDATE employees e
JOIN HighestSalaryEmployees h ON e.id = h.employee_id
SET e.salary = CASE
    WHEN e.salary * 1.1 > h.budget THEN h.budget
    ELSE e.salary * 1.1
END;

这样，代码就更加简洁明了，性能也更好。

SET子句中的CTE：删除的利器

CTE不仅可以用于更新操作，还可以用于删除操作。例如，假设我们需要删除所有重复的邮箱地址，只保留id最小的那一个。

表结构如下：

CREATE TABLE emails (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL
);

INSERT INTO emails (email) VALUES
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]');

没有CTE的时候，你可能会这么写：

DELETE FROM emails
WHERE id NOT IN (
    SELECT min_id
    FROM (
        SELECT MIN(id) AS min_id
        FROM emails
        GROUP BY email
    ) AS subquery
);

这段代码虽然也能实现功能，但是可读性一般。用CTE来改造一下：

WITH DuplicateEmails AS (
    SELECT
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM
        emails
)
DELETE FROM emails
WHERE id IN (SELECT id FROM DuplicateEmails WHERE rn > 1);

我们首先定义了一个CTE DuplicateEmails，它包含了每个邮箱地址的id、email和rn（行号）。然后，我们删除所有rn大于1的记录，也就是重复的邮箱地址。

再举一个更复杂的例子：假设我们有一个orders表，记录了订单的信息，包括id、customer_id和order_date。还有一个customers表，记录了客户的信息，包括id和name。现在，我们需要删除所有没有下过订单的客户。

表结构如下：

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (name) VALUES
('Alice'),
('Bob'),
('Charlie');

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-01-01'),
(1, '2023-01-02'),
(2, '2023-01-03');

用CTE可以这么写：

WITH InactiveCustomers AS (
    SELECT
        c.id
    FROM
        customers c
    LEFT JOIN
        orders o ON c.id = o.customer_id
    WHERE
        o.customer_id IS NULL
)
DELETE FROM customers
WHERE id IN (SELECT id FROM InactiveCustomers);

我们首先定义了一个CTE InactiveCustomers，它包含了所有没有下过订单的客户的id。然后，我们删除所有id在InactiveCustomers中的客户。

CTE的优势总结

• 提高可读性： 将复杂的查询分解成多个小的、易于理解的模块。
• 提高可维护性： 修改某个模块不会影响其他模块。
• 提高性能： MySQL优化器可以更好地优化包含CTE的查询。
• 代码复用： CTE可以在同一个查询中多次引用。

注意事项：

• CTE只能在当前查询中使用，不能跨查询使用。
• CTE不能包含ORDER BY子句（除非在最外层的SELECT语句中）。
• CTE不能包含LIMIT子句。
• CTE不能包含UNION、UNION ALL、INTERSECT或EXCEPT操作符。
• 循环CTE（递归CTE）需要谨慎使用，避免无限循环。

几个高级用法示例：

1. 递归CTE（Recursive CTE）

递归CTE可以用来处理层次结构的数据，比如组织结构、树形结构等。

示例：获取某个部门的所有子部门。

假设我们有一个departments表，包含了部门的id、name和parent_id。

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    parent_id INT
);

INSERT INTO departments (name, parent_id) VALUES
('总公司', NULL),
('研发部', 1),
('测试部', 1),
('后端开发组', 2),
('前端开发组', 2);

要获取研发部（id=2）的所有子部门，可以使用递归CTE：

WITH RECURSIVE SubDepartments AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 2
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    JOIN SubDepartments sd ON d.parent_id = sd.id
)
SELECT * FROM SubDepartments;

这个查询首先选择id为2的部门（研发部），然后递归地选择所有parent_id等于上一次选择的部门的id的部门。

2. 多重CTE

可以在一个查询中定义多个CTE，用逗号分隔。

示例：

WITH CTE1 AS (
    SELECT column1, column2
    FROM table1
    WHERE condition1
),
CTE2 AS (
    SELECT column3, column4
    FROM table2
    WHERE condition2
)
SELECT CTE1.column1, CTE2.column3
FROM CTE1
JOIN CTE2 ON CTE1.column2 = CTE2.column4;

3. CTE与窗口函数结合

CTE经常与窗口函数一起使用，可以实现更复杂的聚合和分析操作。

示例：计算每个部门的工资排名。

WITH RankedSalaries AS (
    SELECT
        id,
        name,
        salary,
        department_id,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
    FROM
        employees
)
SELECT *
FROM RankedSalaries
WHERE salary_rank <= 3; -- 获取每个部门工资排名前三的员工

实战演练：复杂场景下的CTE应用

假设我们有一个电商平台，需要统计每个月的新增用户数和订单总额。

表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    register_date DATE NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

可以使用CTE来完成这个统计：

WITH MonthlyNewUsers AS (
    SELECT
        DATE_FORMAT(register_date, '%Y-%m') AS month,
        COUNT(*) AS new_user_count
    FROM
        users
    GROUP BY
        DATE_FORMAT(register_date, '%Y-%m')
),
MonthlyOrderTotals AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS total_amount
    FROM
        orders
    GROUP BY
        DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    MonthlyNewUsers.month,
    MonthlyNewUsers.new_user_count,
    MonthlyOrderTotals.total_amount
FROM
    MonthlyNewUsers
JOIN
    MonthlyOrderTotals ON MonthlyNewUsers.month = MonthlyOrderTotals.month;

这个查询首先定义了两个CTE：MonthlyNewUsers用于统计每个月的新增用户数，MonthlyOrderTotals用于统计每个月的订单总额。然后，我们将这两个CTE连接起来，得到最终的结果。

总结：掌握CTE，解锁SQL新境界

CTE是MySQL 8.0中一个非常强大的工具，它可以让你编写更简洁、更易读、更易维护的SQL代码。在SET子句中使用CTE，可以实现更复杂的更新和删除操作。熟练掌握CTE，你的SQL功力将会得到质的飞跃。希望今天的讲座能帮助大家更好地理解和使用CTE，在SQL的世界里自由驰骋！

最后，记住：代码写的漂亮，bug自然少！祝大家写出优雅的SQL代码！