SQL进阶——with

在 MySQL 中，WITH 关键字用于定义 公用表表达式（Common Table Expressions，CTE）。CTE 是一个临时的结果集，可以在一个查询中多次引用，通常用于简化复杂查询，提高可读性。

基础语法

WITH cte_name AS (
    -- 子查询
    SELECT ...
)
SELECT ...
FROM cte_name;

• cte_name 是 CTE 的名称。
• 在 WITH 子句中定义的 CTE 可以在后续的 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。

---

示例 1：简单的 CTE

假设有一个 employees 表，我们想查询工资高于平均工资的员工：

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_sal;

• avg_salary 是一个 CTE，计算了员工的平均工资。
• 在主查询中，我们将 employees 表与 avg_salary CTE 进行连接，筛选出工资高于平均工资的员工。

---

示例 2：递归 CTE

CTE 还支持递归查询，常用于处理层次结构数据（如树形结构）。

假设有一个 categories 表，存储了分类及其父分类：

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

我们想查询某个分类及其所有子分类：

WITH RECURSIVE category_tree AS (
    -- 基础查询：选择根节点
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1  -- 假设根节点的 id 是 1

    UNION ALL

    -- 递归查询：选择子节点
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

• WITH RECURSIVE 定义了一个递归 CTE。
• 基础查询选择根节点。
• 递归部分通过 UNION ALL 不断将子节点添加到结果集中，直到没有更多的子节点。

---

示例 3：多个 CTE

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

WITH
    dept_stats AS (
        SELECT department_id, AVG(salary) AS avg_sal
        FROM employees
        GROUP BY department_id
    ),
    high_paid_employees AS (
        SELECT e.name, e.salary, e.department_id
        FROM employees e
        JOIN dept_stats ds ON e.department_id = ds.department_id
        WHERE e.salary > ds.avg_sal
    )
SELECT * FROM high_paid_employees;

• dept_stats CTE 计算每个部门的平均工资。
• high_paid_employees CTE 筛选出工资高于部门平均工资的员工。
• 最后从 high_paid_employees 中选择数据。

---

示例 4：CTE 用于更新或删除

CTE 不仅可以用于 SELECT，还可以用于 UPDATE 或 DELETE。

假设我们想将工资低于平均工资的员工的工资增加 10%：

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
UPDATE employees
SET salary = salary * 1.10
WHERE salary < (SELECT avg_sal FROM avg_salary);

• avg_salary CTE 计算平均工资。
• UPDATE 语句使用 CTE 的结果来更新符合条件的记录。

---

注意事项

1. CTE 的作用范围：CTE 仅在定义它的查询中有效，查询结束后 CTE 就会被销毁。
2. 性能：CTE 可以提高查询的可读性，但在某些情况下可能会影响性能（尤其是递归 CTE）。
3. 递归深度：MySQL 对递归 CTE 的深度有限制，默认是 1000 层，可以通过 cte_max_recursion_depth 参数调整。

---

总结

• WITH 关键字用于定义 CTE，简化复杂查询。
• CTE 可以是普通的（非递归）或递归的。
• CTE 可以用于 SELECT、UPDATE、DELETE 等操作。
• 递归 CTE 适合处理层次结构数据（如树形结构）。