在 MySQL 中，WITH语句（或称为公用表表达式，Common Table Expressions，简称 CTE）用于定义一个临时结果集，可以在查询的其他部分中重复引用。通常用在复杂查询中，方便将查询逻辑分解为多个部分，代码更清晰，并且可以重复使用中间结果。
MySQL 支持两种类型的 CTE：
- 非递归 CTE：基本的
WITH语句，用于定义一次性计算的结果集。 - 递归 CTE：CTE 自己引用自己，通常用于分层数据或树状结构的查询。
下面分别介绍它们的用法和一些常见示例。
1. 非递归 CTE
非递归 CTE 在查询中定义一个固定的结果集，在执行后不会再改变。语法如下：
WITH cte_name AS ( SELECT ... ) SELECT * FROM cte_name;
示例 1：计算部门员工的平均工资
假设有一个employees表，包含员工的department_id、name和salary。
WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg_salary d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;这个查询首先用WITH计算各部门的平均工资（dept_avg_salary），然后找出工资高于部门平均工资的员工。
示例 2：按条件拆分查询
假设要找到销售额最高的 5 位销售人员，可以使用 CTE 进行临时排名：
WITH ranked_sales AS ( SELECT name, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS rank FROM sales_team ) SELECT name, sales_amount FROM ranked_sales WHERE rank <= 5;CTEranked_sales生成了一个带排名的销售记录表，然后主查询从中提取前五名。
2. 递归 CTE
递归 CTE 允许在定义时引用自身，常用于层级结构的查询，比如管理层次结构、树形结构等。语法如下：
WITH RECURSIVE cte_name AS ( SELECT ... -- 初始查询 UNION ALL SELECT ... FROM cte_name -- 递归查询 ) SELECT * FROM cte_name;示例 3：计算阶乘
下面是一个递归 CTE 示例，计算 1 到 5 的阶乘。
WITH RECURSIVE factorial_cte AS ( SELECT 1 AS n, 1 AS factorial UNION ALL SELECT n + 1, factorial * (n + 1) FROM factorial_cte WHERE n < 5 ) SELECT * FROM factorial_cte;这个 CTE 首先定义了n=1和factorial=1的初始值，然后递归地计算 1 到 5 的阶乘。
示例 4：查询部门的层级结构
假设有一个departments表，每个部门都有一个id和parent_id（指向上级部门）。递归 CTE 可以查询从某个部门开始的所有子部门。
WITH RECURSIVE dept_hierarchy AS ( SELECT id, name, parent_id FROM departments WHERE id = 1 -- 从根部门 ID 为 1 开始 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d JOIN dept_hierarchy h ON d.parent_id = h.id ) SELECT * FROM dept_hierarchy;3. 嵌套 CTE 和多 CTE 定义
在一个查询中可以定义多个 CTE，并在查询的其他部分引用它们。这些 CTE 可以相互引用，按顺序处理。
示例 5：多个 CTE 的嵌套查询
假设要查询一组数据的中间计算结果，可以使用嵌套 CTE：
WITH initial_sales AS ( SELECT salesperson_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson_id ), ranked_sales AS ( SELECT salesperson_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM initial_sales ) SELECT salesperson_id, total_sales, sales_rank FROM ranked_sales WHERE sales_rank <= 10;这里，initial_sales计算每个销售人员的总销售额，ranked_sales对销售额进行排名，然后主查询获取前十名销售人员。
4. 使用 CTE 简化复杂查询逻辑
示例 6：复杂查询的分步计算
假设有一个电商订单系统，要求统计每月每个产品的销售额及增长率。
WITH monthly_sales AS ( SELECT product_id, DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(sales_amount) AS total_sales FROM orders GROUP BY product_id, month ), sales_growth AS ( SELECT m1.product_id, m1.month, m1.total_sales, (m1.total_sales - m2.total_sales) / m2.total_sales AS growth_rate FROM monthly_sales m1 LEFT JOIN monthly_sales m2 ON m1.product_id = m2.product_id AND DATE_FORMAT(DATE_SUB(m1.month, INTERVAL 1 MONTH), '%Y-%m') = m2.month ) SELECT * FROM sales_growth;这个查询使用两个 CTE：monthly_sales计算每月的总销售额，sales_growth计算月销售增长率。
总结
WITH语句的多种用法总结如下：
- 非递归 CTE用于分解复杂查询。
- 递归 CTE用于层级数据查询。
- 嵌套 CTE可以组合多个步骤的查询。
- 简化查询逻辑：分解复杂的 SQL 逻辑，使查询更清晰易懂。
CTE 是复杂查询中不可或缺的工具，有助于使代码简洁且易于维护。
WITH RECURSIVE 举例说明，表结构是id和pid的指向 大概有五层
示例：递归查询部门层级
假设有一个departments表，结构如下：
id: 部门 IDname: 部门名称pid: 父级部门 ID（顶级部门的pid为NULL）
表中有五层嵌套的部门数据：
id
name
pid
1
公司
NULL
2
技术部
1
3
市场部
1
4
开发组
2
5
测试组
2
6
前端开发
4
7
后端开发
4
8
大客户市场部
3
9
中小客户市场部
3
递归 CTE 查询：获取指定部门的所有下级部门
我们可以使用递归 CTE 从根部门（例如公司层级的id=1）开始，查询所有子部门并显示层级关系。
wITH RECURSIVE dept_hierarchy AS ( -- 初始查询，获取顶级部门（这里我们从 id=1 的公司开始） SELECT id, name, pid, 1 AS level FROM departments WHERE id = 1 -- 这里可以更改为要查询的根部门的 ID UNION ALL -- 递归查询：找到上级部门（父级）的下一级部门 SELECT d.id, d.name, d.pid, h.level + 1 AS level FROM departments d JOIN dept_hierarchy h ON d.pid = h.id ) SELECT * FROM dept_hierarchy;查询结果解释
这个递归 CTE 分为两部分：
- 初始查询：
SELECT id, name, pid, 1 AS level，从指定的部门（id=1）开始，将其层级设为1。 - 递归查询：从上级部门的
id（即h.id）出发，查找其所有下级部门，并将level加 1，这样层级关系会递归增长，直到没有下级部门。
执行后，结果显示部门的层级关系：
id
name
pid
level
1
公司
NULL
1
2
技术部
1
2
3
市场部
1
2
4
开发组
2
3
5
测试组
2
3
6
前端开发
4
4
7
后端开发
4
4
8
大客户市场部
3
3
9
中小客户市场部
3
3
在这个查询中，level列表示部门的层级，从1开始递增。
