1. 介绍
MySQL 是一种被开发者和数据库管理员广泛使用的数据库管理系统，提供强大且灵活的 SQL 功能。在其众多特性中，WITH 子句（公共表表达式：CTE），自 MySQL 8.0 起引入，是一个能够提升 SQL 查询可读性并改善可维护性的强大工具。
本文从基础到高级使用，系统讲解 WITH 子句，面向初学者到中级用户。特别会涉及 替换子查询 与 实现递归查询 等实用主题。
对于正在学习 SQL 或在日常工作中苦于优化查询的读者，本文旨在提供切实可行的解决方案。请按以下内容阅读，了解 WITH 子句的基本原理并在实际场景中加以运用。
2. WITH 子句的基础（公共表表达式）
什么是 WITH 子句？
WITH 子句 是一种语法，用于在 SQL 查询内部定义临时结果集（公共表表达式，CTE），并在后续查询中引用它。自 MySQL 8.0 起支持，它可以将复杂的子查询以更清晰、更简洁的形式重写。
例如，直接编写子查询时，可读性会下降且整体查询可能变得冗长。使用 WITH 子句可以将查询划分为逻辑块，使其更易于理解。
WITH 子句的基本语法
下面是 WITH 子句的基本语法：
WITH table_name AS (
SELECT column1, column2
FROM original_table
WHERE condition
)
SELECT column1, column2
FROM table_name;
在该语法中，WITH 后面定义了一个虚拟表（公共表表达式），随后在主查询中使用它。这使得经常使用的子查询能够以简洁的方式表达。
与子查询和视图的区别
WITH 子句创建的是临时可用的结果集，在多个方面区别于子查询和视图。
| Feature | WITH Clause | Subquery | View |
|---|---|---|---|
| Scope | Valid only within the query | Usable only where defined | Reusable across the entire database |
| Persistence | Temporary | Temporary | Permanent |
| Purpose | Simplifies complex queries | Temporary data extraction | Frequently reused data extraction |
相较于子查询，WITH 子句更具可读性；当不需要像视图那样创建永久对象时，它是理想的选择。
使用 WITH 子句的好处
- 提升查询可读性 即使存在多个子查询，使用 WITH 子句组织后也能使结构更加清晰。
- 提升可复用性 通过定义临时结果集，可在同一查询中多次引用。
- 提升可维护性 查询可以逻辑划分，修改和扩展变得更容易。
3. MySQL WITH 子句的基本用法
替换子查询
WITH 子句是简化复杂子查询的强大工具。直接嵌入子查询会使整个查询变得繁琐且难以阅读，而使用 WITH 子句则可以提升可读性。
下面给出一个使用 WITH 子句替换子查询的基础示例。
使用子查询：
SELECT AVG(sales.total) AS average_sales
FROM (
SELECT SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS sales;
使用 WITH 子句：
WITH sales AS (
SELECT SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;
在本例中，使用 WITH 子句定义了名为 sales 的临时结果集，然后在主查询中引用它。这使得整个查询更易理解且结构更清晰。
定义多个公共表表达式（CTE）
WITH 子句允许定义多个 CTE，从而进一步实现复杂查询的模块化。
示例：
WITH
sales_per_customer AS (
SELECT customer_id, SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
),
high_value_customers AS (
SELECT customer_id
FROM sales_per_customer
WHERE total_sales > 10000
)
SELECT customer_id
FROM high_value_customers;
在这个例子中，sales_per_customer 计算每个客户的总销售额，并基于该结果，high_value_customers 提取高购买金额的客户。通过顺序使用多个 CTE，可以逐步构建查询。
使用嵌套 CTE
通过使用嵌套 CTE，您可以执行更复杂的数据操作。
示例：
WITH
sales_data AS (
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id
),
ranked_sales AS (
SELECT product_id, total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS rank
FROM sales_data
)
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;
在这个查询中，sales_data 按产品汇总销售额，ranked_sales 根据销售额量分配排名。最后，提取前五名产品。
实际使用要点
- 逻辑步骤思考 逐步构建 CTE 以提高可读性并使调试更容易。
- 存储中间计算结果 将多次使用的计算结果或过滤条件分组到 CTE 中以减少代码重复。
- 小心处理大型数据集 由于 CTE 生成临时结果集，在处理大量数据时要考虑性能影响。
4. 递归 WITH 子句的实际示例
什么是递归 WITH 子句？
递归 WITH 子句（递归 CTE）是一种使用公共表表达式（CTE）重复执行自引用查询的方法，从而能够处理层次数据和迭代计算。递归 CTE 在 MySQL 8.0 及更高版本中得到支持，在处理父子关系和层次结构时特别有用。
递归 CTE 的基本语法
要定义递归 CTE，请使用 WITH RECURSIVE 关键字。基本语法如下：
WITH RECURSIVE recursive_table_name AS (
initial_query -- starting point of the recursion
UNION ALL
recursive_query -- query called recursively
)
SELECT * FROM recursive_table_name;
- 初始查询 : 检索递归过程的第一个数据集。
- 递归查询 : 基于初始查询或前一次迭代的结果生成新行。
- UNION ALL : 组合初始查询和递归查询的结果。
示例：处理层次数据
递归 CTE 常用于展开具有层次结构的数据（例如，组织树或类别树）。
示例：展开员工管理层次结构
考虑以下 employees 表：
| employee_id | name | manager_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
使用此数据，我们可以创建一个从给定员工开始检索完整层次结构的查询。
WITH RECURSIVE employee_hierarchy AS (
-- Initial query: get top-level employees
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive query: get direct reports
SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh
ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
结果：
| employee_id | name | manager_id | level |
|---|---|---|---|
| 1 | Alice | NULL | 1 |
| 2 | Bob | 1 | 2 |
| 3 | Charlie | 1 | 2 |
| 4 | David | 2 | 3 |
在这个查询中，根据 manager_id 递归搜索下属，展开完整层次结构。
递归 CTE 的限制和注意事项
- 需要结束条件 如果递归查询不满足结束条件，可能会发生无限循环。始终包含适当的条件以防止无限递归。
- 性能影响 递归 CTE 可能涉及对大型数据集的许多计算，这可能会增加执行时间。使用
LIMIT子句和过滤条件来提高效率。 - 递归深度限制 MySQL 对递归深度有限制，因此在运行非常深的递归处理时要小心。此限制可以通过
max_recursive_iterations参数配置。
递归CTE的实用场景
- 遍历文件夹结构：递归搜索文件夹及子文件夹。
- 构建组织结构图：可视化从管理者到下属的层级关系。
- 显示分类树：检索层级化的产品分类或标签结构。
递归CTE是一种强大的方式，可为这些场景编写简洁的SQL查询，同时提升可读性。
5. 使用 WITH 子句的注意事项与考虑
性能影响与优化
- CTE 重新计算：一般来说，使用 WITH 子句定义的CTE在每次被引用时都会重新计算。因此，多次使用相同的CTE会增加查询执行时间。示例：
WITH sales AS ( SELECT product_id, SUM(amount) AS total_sales FROM orders GROUP BY product_id ) SELECT * FROM sales WHERE total_sales > 1000; SELECT COUNT(*) FROM sales;
在上述情况下，sales 被引用了两次，因此计算了两次。为避免这种情况，当需要多次引用结果时，将结果存入临时表会更有效。
解决方案：
CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;
SELECT * FROM temp_sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM temp_sales;
- 拆分复杂的CTE：如果 WITH 子句嵌套层次过深，整体查询会变得复杂且难以调试。应适当拆分逻辑，避免单个CTE内部的处理过于繁复。
在大数据集上使用 WITH 子句
WITH 子句在执行期间会生成临时数据集。处理大量数据时，这可能会对内存或存储造成负担。
对策：
- 使用 WHERE 子句过滤数据：通过在CTE内部过滤不必要的数据来减少计算量。
WITH filtered_orders AS ( SELECT * FROM orders WHERE order_date > '2023-01-01' ) SELECT customer_id, SUM(amount) FROM filtered_orders GROUP BY customer_id;
- 使用 LIMIT 子句：如果数据集很大，使用
LIMIT只提取所需的数据。
MySQL 版本兼容性
MySQL 中的 WITH 子句在 MySQL 8.0 及以上版本受支持。由于早期版本不支持 WITH 子句，需要考虑替代方案。
替代方案：
- 使用子查询：直接使用子查询而不是 WITH 子句。
SELECT AVG(total_sales) FROM ( SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id ) AS sales;
- 创建视图：如果需要可重用的查询，使用视图会更有效。
CREATE VIEW sales_view AS SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id; SELECT AVG(total_sales) FROM sales_view;
正确使用 WITH 子句的方法
- 优先考虑可读性：WITH 子句的目的是组织查询并提升可读性。过度使用会使查询更复杂，故仅在必要时使用。
- 验证性能：检查执行计划（
EXPLAIN命令），并考虑如何优化性能。EXPLAIN WITH sales AS ( SELECT product_id, SUM(amount) AS total_sales FROM orders GROUP BY product_id ) SELECT * FROM sales WHERE total_sales > 1000;
6. 实际场景中的实用案例
销售数据聚合
下面是一个按月聚合销售数据的示例，随后使用该结果计算月均销售额。
示例：按月聚合销售并计算平均值
WITH monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
SUM(amount) AS total_sales
FROM orders
GROUP BY sales_month
)
SELECT
sales_month,
total_sales,
AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;
在此查询中，monthly_sales 计算每月的销售额，并基于该结果计算整体的平均销售额。这使得数据能够清晰地组织，并简化了分析。
基于特定条件过滤数据
通过将复杂的过滤逻辑拆分到 WITH 子句中，可以提升可读性。
示例：创建高消费客户列表
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_spent
FROM customer_totals
WHERE total_spent > 100000;
在此查询中，customer_totals 计算每位客户的总购买金额，并提取满足指定条件的客户。
分析层次结构数据
在分析组织结构或类别等层次结构数据时，递归的 WITH 子句非常有用。
示例：检索直接和间接下属列表
WITH RECURSIVE employee_hierarchy AS (
SELECT
employee_id,
name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.name,
e.manager_id,
eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh
ON e.manager_id = eh.employee_id
)
SELECT
employee_id,
name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, manager_id;
此查询在 employee_hierarchy 中构建层次结构数据，并按层级检索员工。它实现了类似组织结构图的信息的动态生成。
使用多个 CTE 的高级分析
通过利用多个 WITH 子句，数据可以一步步处理，使复杂分析更为简化。
示例：提取每个类别的畅销产品
WITH category_sales AS (
SELECT
category_id,
product_id,
SUM(amount) AS total_sales
FROM orders
GROUP BY category_id, product_id
),
ranked_sales AS (
SELECT
category_id,
product_id,
total_sales,
RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
FROM category_sales
)
SELECT
category_id,
product_id,
total_sales
FROM ranked_sales
WHERE rank <= 3;
在此查询中，计算每个类别的销售额，并提取每个类别中排名前三的产品。当基于特定条件缩小数据范围时，这种方法非常有效。
实际应用的关键要点
- 在逻辑阶段设计查询 使用 WITH 子句将查询划分为多个阶段，逐步处理数据，同时保持可读性。
- 仅提取必要的数据 使用 WHERE 和 LIMIT 子句避免处理不必要的数据，设计高效的查询。
- 灵活的业务应用 WITH 子句可灵活用于销售分析、客户细分、库存管理等多种场景。
7. 常见问题 (FAQ)
Q1：何时使用 WITH 子句？
A1：
WITH 子句在以下情形中特别有效：
- 当您想简化复杂的子查询时。
- 当您需要在查询中多次重用相同的数据集时。
- 当您希望将查询逻辑划分以提升可读性时。
例如，在多次使用相同聚合结果的查询中，WITH 子句能够更高效地组织查询。
Q2：递归 CTE 何时有用？
A2：
递归 CTE 在处理层次结构或迭代计算时非常有用。具体如下：
- 处理层次结构数据（例如组织树、类别结构）。
- 显示文件夹或文件层次结构。
- 对数字或时间段进行顺序计算（例如计算斐波那契数列）。
使用递归 CTE 可以更轻松地展开和处理自引用数据。
Q3: 使用 WITH 子句的查询是否比视图更高效？
A3:
这取决于具体使用场景。
- WITH 子句：创建仅在查询内部使用的临时结果集。适用于不需要频繁复用的数据。
- 视图：永久存储在数据库中，可被其他查询重复使用。适用于需要反复使用的查询。
根据具体情形选择合适的方式很重要。
Q4: 使用 WITH 子句时导致性能下降的原因是什么？
A4:
使用 WITH 子句导致性能下降的主要原因包括：
- CTE 重新计算：每次引用时都会重新计算结果，增加处理时间。
- 处理大数据集：在 CTE 中生成大数据集会增加内存使用并降低性能。
- 缺乏适当的索引：如果 CTE 内的查询未使用合适的索引，性能可能会变慢。
对策：
- 如果复用频率高，可考虑使用临时表或视图。
- 使用 WHERE 和 LIMIT 子句合理地缩小数据范围。
Q5: 对于不支持 WITH 子句的 MySQL 版本，有哪些替代方案？
A5:
在 MySQL 8.0 之前的版本中不支持 WITH 子句，可使用以下替代方案：
- 使用子查询：直接使用子查询来代替 WITH 子句。
SELECT AVG(total_sales) FROM ( SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id ) AS sales;
- 使用临时表：将可复用的数据集存储在临时表中。
CREATE TEMPORARY TABLE temp_sales AS SELECT customer_id, SUM(amount) AS total_sales FROM orders GROUP BY customer_id; SELECT AVG(total_sales) FROM temp_sales;
Q6: 使用 WITH 子句时的最佳实践是什么？
A6:
请牢记以下最佳实践：
- 优先保持简洁：不要将复杂逻辑强行塞入单个 WITH 子句，应适当拆分。
- 验证性能：使用
EXPLAIN命令检查执行计划，并根据需要进行优化。 - 考虑可复用性：如果复用频率高，建议使用视图或临时表。
8. 结论
本文介绍了 MySQL 8.0 引入的 WITH 子句（公共表表达式，CTE），从基础到高级应用。WITH 子句是使复杂查询简洁易读的极其有用的特性。以下是关键要点。
WITH 子句的主要优势
- 提升查询可读性：组织复杂的子查询，增强 SQL 代码的可读性和可维护性。
- 查询可复用性：在多次引用同一数据集时，高效地处理数据。
- 支持递归数据操作：递归 CTE 简化了层级数据和迭代计算的处理。
实际使用要点
- 适用于 销售和客户数据分析，实现逐步聚合。
- 递归 CTE 对 层级数据处理（如组织结构图或分类结构）非常有效。
- 将 WITH 子句与视图或临时表结合使用，可实现灵活高效的数据库操作。
重要注意事项
- WITH 子句功能强大，但不当使用可能导致性能下降。
- 根据具体情况评估可复用性和性能，适时在视图和临时表之间做选择。
- 始终使用执行计划（
EXPLAIN命令）验证查询效率。
后续步骤
使用 WITH 子句，您可以编写更高效、可维护的 SQL 查询。可按以下步骤在实际项目中尝试应用：
- 从简单查询开始，练习使用 WITH 子句组织结构。
- 挑战递归 CTE，处理层级数据和复杂场景。
- 注重性能优化，进一步提升 SQL 技能。
本文到此结束。请在日常工作和学习中运用您对 MySQL WITH 子句的知识。
