MySQL - 公用表表达式

MySQL - 公共表表达式（CTE，WITH 子句）

公共表表达式（Common Table Expression，简称 CTE）是一个命名的临时结果集，它仅在单个 SQL 语句（如 SELECT、INSERT、UPDATE 或 DELETE）的持续时间内存在。CTE 在 MySQL 8.0 中引入，使用 WITH 子句定义，是提高复杂查询可读性和结构性的强大工具。

为什么要使用 CTE？

可读性： CTE 允许您将冗长复杂的查询分解为逻辑清晰、易于阅读的块。这使得您的 SQL 代码更容易理解和维护。
可重用性： 您可以在单个查询中多次引用同一个 CTE，避免重复相同的子查询逻辑。
简化复杂的 JOIN 和聚合： 它们允许您在与其他表连接之前预处理或预聚合数据。
递归查询： CTE 可以引用自身，使您能够查询分层数据结构，如组织架构图或物料清单。（这是一个此处未涵盖的高级主题）。

非递归 CTE 语法

WITH cte_name_1 AS (
    -- 定义第一个 CTE 的子查询
    SELECT ...
),
cte_name_2 AS (
    -- 定义第二个 CTE 的子查询，可以引用 cte_name_1
    SELECT ...
)
-- 使用 CTE 的最终查询
SELECT ...
FROM cte_name_1
JOIN cte_name_2 ON ...;

实际示例

让我们设置一些示例表来演示实际用例。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

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

INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');

INSERT INTO employees VALUES
(101, 'John', 'Doe', 2, 90000),
(102, 'Jane', 'Smith', 2, 95000),
(103, 'Peter', 'Jones', 1, 75000),
(104, 'Mary', 'Jane', 1, 80000),
(105, 'Sue', 'Storm', 3, 60000);

示例 1：查找薪资高于其部门平均水平的员工

如果没有 CTE，这将需要一个相关子查询或一个混乱的连接。CTE 使逻辑清晰明了：首先，计算每个部门的平均薪资，然后将该结果连接回员工表。

WITH DepartmentAverages AS (
    -- 步骤 1：计算每个部门的平均薪资
    SELECT
        department_id,
        AVG(salary) as avg_salary
    FROM
        employees
    GROUP BY
        department_id
)
-- 步骤 2：将结果连接回来，查找薪资高于平均水平的员工
SELECT
    e.first_name,
    e.last_name,
    d.name AS department_name,
    e.salary,
    da.avg_salary
FROM
    employees e
JOIN
    DepartmentAverages da ON e.department_id = da.department_id
JOIN
    departments d ON e.department_id = d.id
WHERE
    e.salary > da.avg_salary;

输出

名	姓	部门名称	薪资	平均薪资
Jane	Smith	Engineering	95000.00	92500.00
Mary	Jane	Sales	80000.00	77500.00

示例 2：组合来自多个表的数据

此示例演示了如何在最终连接之前使用多个 CTE 来预过滤和整形来自不同来源的数据。这是原始教程中多表示例的更正版和更有意义的版本。

WITH
-- CTE 1：只选择高级工程师
SeniorEngineers AS (
    SELECT id, first_name, last_name, department_id
    FROM employees
    WHERE department_id = 2 AND salary >= 95000
),
-- CTE 2：获取部门名称
DepartmentInfo AS (
    SELECT id, name AS department_name
    FROM departments
)
-- 最终查询：连接两个 CTE 以获取最终报告
SELECT
    se.first_name,
    se.last_name,
    di.department_name
FROM
    SeniorEngineers se
JOIN
    DepartmentInfo di ON se.department_id = di.id;

输出

名	姓	部门名称
Jane	Smith	Engineering

关键要点

CTE 在 MySQL 8.0 及更高版本中可用。
它们使用 WITH 子句定义，是临时的，仅在紧随其后的单个语句中有效。
使用 CTE 可以使您的复杂查询更具模块化、可读性和可维护性。它们是现代高质量 SQL 的标志。