WITH在MYSQL中的用法

WITH 子句（也称为公共表表达式，Common Table Expression，简称 CTE）是 SQL 中一种强大的查询构建工具，它可以显著提高复杂查询的可读性和可维护性。

一、基本语法结构

WITH cte_name AS (
    SELECT ...  -- 定义CTE的查询
)
SELECT ... FROM cte_name;  -- 主查询使用CTE

二、CTE 的核心特点

1. 临时结果集：CTE 只在当前查询执行期间存在
2. 可引用性：定义后可在主查询中多次引用
3. 作用域限制：仅在紧随其后的单个语句中有效

三、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 的优势

1. 提高可读性：

   • 将复杂查询分解为逻辑块
   • 类似编程中的变量定义

2. 避免重复子查询：

   -- 不使用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...
   

3. 支持递归查询：处理树形/层次结构数据

五、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;

七、性能注意事项

1. 物化提示：

   WITH cte_name AS (
       SELECT /*+ MATERIALIZE */ ...  -- 强制物化
   )
   

2. 合并提示：

   WITH cte_name AS (
       SELECT /*+ MERGE */ ...  -- 强制合并到主查询
   )
   

3. 递归深度控制（MySQL 默认 1000）：

   SET @@cte_max_recursion_depth = 2000;
   

八、版本兼容性

• MySQL 8.0+ 完整支持 CTE 和递归 CTE
• MySQL 5.7 及更早版本不支持 CTE

WITH 子句是现代 SQL 开发中不可或缺的工具，合理使用可以大幅提升查询的清晰度和维护性，特别是在处理多层嵌套或递归数据时。