with 子句（也称为公共表表达式，common table expression，简称 cte）是 sql 中一种强大的查询构建工具，它可以显著提高复杂查询的可读性和可维护性。
一、基本语法结构
with cte_name as (
select ... -- 定义cte的查询
)
select ... from cte_name; -- 主查询使用cte
二、cte 的核心特点
- 临时结果集：cte 只在当前查询执行期间存在
- 可引用性：定义后可在主查询中多次引用
- 作用域限制：仅在紧随其后的单个语句中有效
三、mysql 中 cte 的具体用法
1. 基本 cte（单表表达式）
with sales_summary as (
select product_id, sum(quantity) as total_sold
from orders
group by product_id
)
select p.product_name, s.total_sold
from products p
join sales_summary s on p.product_id = s.product_id;
2. 多 cte 定义（逗号分隔）
with
customer_orders as (
select customer_id, count(*) as order_count
from orders
group by customer_id
),
high_value_customers as (
select customer_id
from customer_orders
where order_count > 5
)
select c.customer_name
from customers c
join high_value_customers h on c.customer_id = h.customer_id;
3. 递归 cte（mysql 8.0+ 支持）
递归 cte 用于处理层次结构数据：
with recursive org_hierarchy as (
-- 基础查询（锚成员）
select id, name, parent_id, 1 as level
from organization
where parent_id is null
union all
-- 递归查询（递归成员）
select o.id, o.name, o.parent_id, h.level + 1
from organization o
join org_hierarchy h on o.parent_id = h.id
)
select * from org_hierarchy;
四、cte 的优势
提高可读性：
避免重复子查询：
-- 不使用cte（重复子查询）
select * from (select ... from table1) as t1
join (select ... from table1) as t2...
-- 使用cte（避免重复）
with t1 as (select ... from table1)
select * from t1 join t1 as t2...
支持递归查询：处理树形/层次结构数据
五、cte 与临时表的区别
| 特性 | cte | 临时表 |
|---|
| 生命周期 | 仅当前语句有效 | 会话结束前有效 |
| 存储 | 不物理存储 | 可能存储在内存或磁盘 |
| 索引 | 不能创建索引 | 可以创建索引 |
| 可见性 | 仅定义它的查询可见 | 同一会话的后续查询可见 |
| 性能 | 优化器可能内联展开 | 需要实际创建和填充 |
六、实际应用场景
1. 复杂报表查询
with
monthly_sales as (...),
product_ranking as (...)
select ... from monthly_sales join product_ranking...
2. 数据清洗管道
with
raw_data as (...),
cleaned_data as (...),
enriched_data as (...)
select * from enriched_data;
3. 层次结构遍历（组织架构、评论线程等）
with recursive comment_tree as (...)
select * from comment_tree;
七、性能注意事项
物化提示：
with cte_name as (
select /*+ materialize */ ... -- 强制物化
)
合并提示：
with cte_name as (
select /*+ merge */ ... -- 强制合并到主查询
)
递归深度控制（mysql 默认 1000）：
set @@cte_max_recursion_depth = 2000;
八、版本兼容性
- mysql 8.0+ 完整支持 cte 和递归 cte
- mysql 5.7 及更早版本不支持 cte
with 子句是现代 sql 开发中不可或缺的工具，合理使用可以大幅提升查询的清晰度和维护性，特别是在处理多层嵌套或递归数据时。
到此这篇关于with在mysql中的用法示例详解的文章就介绍到这了,更多相关mysql with用法内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
