MySQL CTE 让复杂查询更优雅

前言

在使用MySQL做业务开发的时候，经常会遇到多层嵌套的查询场景，为解决这类问题Mysql8.0提供了CTE（Common Table Expression，公共表表达式），在实际开发过程中如果子查询如果嵌套层级过多会形成查询黑洞，不仅编写时容易出错，后续维护人员需要从内到外逐层拆解才能理解逻辑。而CTE可以将每个层级的查询拆分成独立的CTE，逻辑顺序清晰，就像写代码时的函数调用一样。

使用

CTE是一种临时的结果集，它仅在当前SQL语句的执行期间有效，就像一个”临时视图”，但比视图更灵活，不需要预先创建。CTE可以将复杂的查询逻辑拆分成多个独立的、逻辑清晰的部分。

MySQL中CTE的核心语法结构:

-- 单个CTE的情况
WITH cte_name (column1, column2, ...)  -- 可选：指定CTE的列名
AS (
    -- CTE的查询逻辑，返回的结果集结构需与指定列名匹配
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- 使用CTE，这里可以是SELECT、INSERT、UPDATE、DELETE等语句
SELECT * FROM cte_name;

-- 多个CTE的情况（用逗号分隔）
WITH 
cte1 AS (SELECT ... FROM ...),
cte2 AS (SELECT ... FROM cte1 ...),  -- 后续CTE可引用前面定义的CTE
cte3 AS (SELECT ... FROM cte2 ...)
SELECT * FROM cte3;

• WITH 关键字：用于标识CTE的开始，是CTE语法的标志。
• cte_name：CTE的名称，建议语义化，方便理解。
• (column1, column2, ...)：可选参数，用于指定CTE结果集的列名。如果不指定，将默认使用CTE内部查询语句返回的列名。当内部查询有列别名、聚合函数时，建议显式指定列名，避免歧义。
• AS ()：AS关键字用于连接CTE名称和查询逻辑，括号内是完整的SELECT语句，用于生成CTE的结果集，这部分是CTE的核心。
• 使用部分：CTE定义完成后，必须立即使用，不能单独定义不使用。使用方式与操作普通表类似，可以通过SELECT查询，也可以用于INSERT、UPDATE、DELETE等DML操作。

递归使用，查询查询各部门的名称、部门内员工数量以及该部门的平均工资，仅显示平均工资大于15000的部门
表结构：

-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    parent_dept_id INT DEFAULT 0  -- 父部门ID，0表示一级部门
);

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 插入测试数据
INSERT INTO departments (dept_name, parent_dept_id) 
VALUES ('总部', 0), ('技术部', 1), ('研发组', 2), ('测试组', 2), ('市场部', 1), ('销售组', 5);

INSERT INTO employees (emp_name, dept_id, salary, hire_date)
VALUES ('张三', 3, 15000, '2020-01-15'), ('李四', 3, 18000, '2019-05-20'),
       ('王五', 4, 12000, '2021-03-10'), ('赵六', 6, 20000, '2018-09-01'),
       ('钱七', 6, 16000, '2020-07-30'), ('孙八', 2, 25000, '2017-11-05'),
       ('周九', 5, 18000, '2019-02-18'), ('吴十', 1, 30000, '2016-08-22');

传统方式子查询方式

SELECT 
    d.dept_name,
    t.emp_count,
    t.avg_salary
FROM departments d
JOIN (
    SELECT 
        dept_id,
        COUNT(emp_id) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
    HAVING AVG(salary) > 15000
) t ON d.dept_id = t.dept_id;

CET 方式：

WITH dept_salary_stats (dept_id, emp_count, avg_salary)
AS (
    SELECT 
        dept_id,
        COUNT(emp_id) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
    HAVING AVG(salary) > 15000
)
SELECT 
    d.dept_name,
    s.emp_count,
    s.avg_salary
FROM departments d
JOIN dept_salary_stats s ON d.dept_id = s.dept_id;

两种方式返回的结果完全一致，但CTE将统计部门工资信息逻辑拆分成了独立的dept_salary_stats，主查询只关注部门表与统计结果关联，逻辑更清晰

优势

1. 简化复杂嵌套查询，提升可读性：CTE可以将每个层级的查询拆分成独立的CTE，逻辑顺序清晰，就像写代码时的函数调用一样。
2. 支持递归查询，解决层级数据问题：对于树形结构数据（如部门层级、菜单结构）、图结构数据，传统查询很难高效处理，而递归CTE可以通过自身调用，轻松遍历层级关系。
3. 避免重复计算，提升开发效率：一个查询逻辑需要在SQL中多次使用（比如多次引用同一个子查询结果），传统方式要么重复编写子查询，要么创建临时表。重复编写会导致代码冗余，临时表则需要手动管理生命周期。而CTE定义一次后，在整个SQL语句中可以多次引用。
4. 与主查询逻辑隔离，便于调试：CTE的逻辑是独立的，可以单独提取CTE的查询语句进行测试，验证其返回结果是否符合预期，再将其整合到主查询中。
5. CTE和子查询的性能基本持平，因为MySQL优化器会将简单CTE转换为类似子查询的执行计划，两者在扫描行数、索引使用上完全一致，对于复杂查询（如多层嵌套+聚合），CTE的执行计划可能更优，因为优化器可以更好地识别CTE之间的依赖关系，减少重复计算。

使用场景

1. 存在多层嵌套的复杂查询（如多表关联+聚合+过滤的组合场景）；
2. 需要处理树形/层级数据（部门层级、菜单结构、评论回复层级等）；
3. 同一查询逻辑需要在SQL中多次复用的场景；
4. 需要提升SQL语句可读性和可维护性的场景；

总结

仅MySQL 8.0及以上版本支持CTE，如果在使用5.x版本，是不能使用的。CTE作为一种优雅的查询方式，其核心价值在于提升代码的可读性和可维护性，尤其是在团队协作开发中，使用CTE可以让你的SQL语句更易于理解和调试。建议在复杂查询中多尝试使用CTE。其次PostgreSQL15+ 和SqlServe等也都支持CTE。