SQL进阶——with

原创于 2025-01-29 12:14:18 发布 · 627 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

在 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);