MySQL SQL查询新模式CTE使用详解

坚持走自己路

目录

• 前言
• 1、成果收益
• 2、背景
• 3、什么是CTE
• 4、CTE如何使用
• 基本语法
• 多个 CTE 的使用
• 示例：
• 5、经验总结

前言

本文将为开发者系统解析MySQL 8.0引入的CTE特性。通过真实业务场景案例，您将掌握：

1. 如何用CTE重构嵌套噩梦般的SQL语句
2. 递归查询实现树形结构遍历的核心方法
3. 通过查询复用提升30%以上执行效率的技巧
4. CTE在复杂业务场景下的最佳实践方案

1、成果收益

本最佳实践已取得的成果或预期收益。

简单说，简化了复杂的SQL查询，提升了复杂查询的可读性和复用性。

2、背景

讲述一下问题或痛点，为什么要做这件事。

计费规则根据险种进行数据统计，计费规则从地区到具体险种配置，地区-计费-参保方案-参保方案详情，层级深，关系一层套一层，很多表数据需要复用，只能反反复复的查询。最后写出一个层层嵌套的SQL，看也看不懂，改也改不动。

传统方案面临三大难题：

1. 嵌套黑洞：5层以上子查询导致SQL可读性断崖式下降
2. 重复炼狱：相同子查询在多个地方重复出现
3. 调试噩梦：修改一个字段需要追踪多级嵌套

3、什么是CTE

公共表表达式（Common Table Expression，简称 CTE），CTE 是在SQL查询中定义的一个临时结果集，CTE 通常用于

• • 多层嵌套子查询的简化
  • 递归查询（如树形结构遍历，MySQL 8.0.1+支持递归CTE）
  • 多次复用同一子查询结果

CTE 的定义部分类似于创建一个临时视图，但其生命周期仅限于当前查询。通过 WITH 创建临时命名结果集，WITH 子句更轻量，适合在单个查询中复用中间结果，提升复杂查询的可读性和复用性。

4、CTE如何使用

基本语法

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

• cte_name：CTE 的名称，就是临时表的名称。
• 子查询：定义 CTE 的结果集。
• 后续查询：可以引用 CTE 名称。

注意with是不需要分号结尾的，当分号出现时就意味着当前的SQL生命周期已经结束了。

多个 CTE 的使用

可以在一个 WITH 子句中定义多个 CTE，用逗号分隔。

WITH 
cus_totals AS (
    SELECT cus_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY cus_id
),
latest_orders AS (
    SELECT cus_id, MAX(order_date) AS latest_date
    FROM orders
    GROUP BY cus_id
)
SELECT t.cus_id, t.total_amount, l.latest_date
FROM cus_totals t
JOIN latest_orders l ON t.cus_id = l.cus_id;

示例：

1. 第一个 CTE：计算每个客户的订单总金额。
2. 第二个 CTE：计算每个客户的最新订单日期。
3. 最终查询：连接两个 CTE，生成结果。

5、经验总结

主要用于数据导出的场景，更方便多表查询的场景。