MySQL CTE 怎样避免重复写复杂子查询

我在整理一条报表 SQL 时，最容易失控的地方不是 JOIN 本身，而是同一段“已完成订单筛选 + 金额聚合”被复制到三个位置：一次用于列表，一次用于总数，一次用于比较上月结果。MySQL CTE 可以把这段逻辑放进 WITH 子句，先起一个有意义的名字，再在同一条语句中重复引用。

这篇只解决一个问题：怎样用 CTE 避免重复写复杂子查询，同时不把“写得更清楚”误解成“执行时一定物化”。官方文档地址：https://dev.mysql.com/doc/refman/8.4/en/with.html。

先把重复子查询抽成一个命名结果

CTE 的基本形态是 WITH cte_name AS (subquery) SELECT ...。括号中的查询先获得一个临时的逻辑名称，后面的主查询可以像引用表一样引用它。它的价值首先是组织代码：筛选条件只写一次，主查询表达“从哪个结果继续处理”，阅读成本会明显下降。

WITH paid_orders AS (
    SELECT
        customer_id,
        order_id,
        total_amount
    FROM orders
    WHERE status = 'PAID' -- 只保留已支付订单，避免每个子查询重复写状态条件
)
SELECT
    po.customer_id,
    COUNT(*) AS order_count,
    SUM(po.total_amount) AS paid_amount
FROM paid_orders AS po
GROUP BY po.customer_id;

这个例子把“哪些订单算已支付”集中在 paid_orders。如果后面的报表还需要按客户 JOIN 画像表、按地区筛选或计算金额区间，可以继续引用这个名字，不再复制整段订单筛选。

让多个 CTE 按依赖顺序组合

复杂报表通常不是一个子查询，而是“基础行集 → 聚合结果 → 最终关联”几层关系。CTE 可以在同一个 WITH 中定义多个名称，后定义的 CTE 可以引用前面已经定义的 CTE。写作时我会让每层只回答一个问题：基础层负责筛选，统计层负责聚合，展示层负责关联。

WITH paid_orders AS (
    SELECT customer_id, order_id, total_amount, paid_at
    FROM orders
    WHERE status = 'PAID' -- 基础层统一订单口径
), customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS paid_amount,
        MAX(paid_at) AS last_paid_at
    FROM paid_orders
    GROUP BY customer_id -- 统计层只消费基础层，避免重复筛选
), ranked_customers AS (
    SELECT
        customer_id,
        order_count,
        paid_amount,
        last_paid_at,
        DENSE_RANK() OVER (ORDER BY paid_amount DESC) AS amount_rank -- 给客户金额排名
    FROM customer_totals
)
SELECT
    rc.amount_rank,
    rc.customer_id,
    c.customer_name,
    rc.order_count,
    rc.paid_amount,
    rc.last_paid_at
FROM ranked_customers AS rc
JOIN customers AS c ON c.customer_id = rc.customer_id -- 展示层补充客户名称
WHERE rc.amount_rank 

多个 CTE 的逗号不是多条 SQL，而是同一个 WITH 子句里的定义分隔符。注意依赖方向：customer_totals 可以读 paid_orders，但不能反过来引用后定义的名称。把每层输出列控制在下游真正需要的范围内，也有助于后面查看执行计划。

列名和作用域要先固定下来

CTE 在一条语句内生效，不是持久化视图，也不会在下一条 SQL 中继续存在。它的名称只在当前语句的作用域中可见。实际开发里最常见的坑是两种：多个输入列同名导致结果列含义不清，以及在 CTE 外误以为还能继续使用 CTE 名称。

WITH customer_totals (customer_id, paid_amount) AS (
    SELECT
        customer_id,
        SUM(total_amount) AS paid_amount -- 显式声明输出列，减少列名漂移
    FROM orders
    WHERE status = 'PAID' -- 统一业务口径
    GROUP BY customer_id
)
SELECT
    ct.customer_id,
    ct.paid_amount,
    CASE
        WHEN ct.paid_amount >= 10000 THEN '高价值' -- 分类逻辑放在最终查询，便于调整
        ELSE '普通'
    END AS customer_level
FROM customer_totals AS ct;

如果 CTE 输出的列名已经稳定，主查询中仍建议使用别名限定列，例如 ct.paid_amount。这样当后续再 JOIN 一张也有 customer_id 或 paid_amount 的表时，不会因为列名冲突而让 SQL 语义变得模糊。

递归 CTE 是另一种能力，适合组织层级关系或生成依赖前一行结果的序列；普通的重复子查询不需要递归。不要为了“层次多”就加 RECURSIVE，否则读者会误以为查询包含递归终止条件和深度控制。

别把 CTE 复用直接等同于物化

CTE 解决的是 SQL 的表达和复用问题，但不代表 MySQL 一定先把它完整写入临时表。MySQL 官方文档说明，派生表、视图引用和 CTE 相关查询块可能采用两种策略：把查询块合并进外层，或者物化为内部临时表。具体选择由查询形状和优化器决定。

这意味着“我只写了一次复杂子查询”与“数据库只执行一次这段逻辑”是两个不同命题。一个 CTE 被多个位置引用时，不能凭名字猜执行次数；需要结合实际数据量和执行计划判断。

EXPLAIN FORMAT=TREE
WITH paid_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE status = 'PAID' -- 计划检查针对同一筛选口径
)
SELECT
    po.customer_id,
    SUM(po.total_amount) AS paid_amount
FROM paid_orders AS po
GROUP BY po.customer_id;

我会把 EXPLAIN 当作性能判断入口，而不是把 CTE 当成性能开关。先看过滤是否尽早发生、索引是否被利用、聚合输入是否过大，再决定是否需要重写查询、拆成临时表或调整索引。若只是为了减少重复文字，CTE 往往已经足够；若需要在多条语句之间复用中间结果，CTE 的生命周期就不合适了。

什么时候用 CTE，什么时候换方案

可以按复用范围做一个简单选择：

还有一个实际取舍：CTE 名称要表达业务结果，而不是表达“第几层”。像 paid_orders、customer_totals 比 tmp1、step2 更容易让后来维护的人判断列的含义。命名清晰时，CTE 才真正替代了重复子查询，而不是把一大段 SQL 挪到文件顶部。

写完 CTE 后我会保留的几个习惯

• 先确认 MySQL 版本支持当前语法，再把重复逻辑抽成 CTE。
• 一个 CTE 尽量承担一个明确任务，基础筛选和展示分类不要混成一团。
• 给输出列和表别名加上稳定命名，尤其是多表 JOIN 时。
• 需要性能结论时看实际 EXPLAIN，不要仅凭“用了 WITH”推断物化或只执行一次。
• 跨语句复用、需要中间结果索引或需要反复调试时，再考虑临时表。

结语：CTE 先解决重复表达，再单独判断性能

MySQL CTE 最直接的用法，是把复杂筛选或聚合子查询命名，然后在同一条 SQL 中按依赖关系组合。它能减少复制、改善结构，但不会替开发者决定最终执行计划。把列名、作用域和 CTE 生命周期写清楚，再用 EXPLAIN 判断合并或物化，才是比较稳妥的落地方式。

相关问题

• 一个 CTE 能被多个 JOIN 重复引用吗？ 可以，同一条语句的后续查询块可以引用它；但执行策略要看优化器，不能只凭写法判断成本。
• CTE 和临时表最大的区别是什么？ CTE 通常只在当前语句内有效，临时表可以跨多条语句使用，也能单独管理索引。
• 为什么写了 CTE 还是感觉查询很慢？ CTE 主要改善表达结构，性能仍取决于过滤、连接、聚合、索引和优化器选择。
• 什么时候应该改成视图？ 当查询定义需要长期保存、被多条业务语句稳定复用，并且权限与版本管理已经纳入数据库治理时，可以考虑视图。