mysql WITH的多种用法与示例

53 2个月前

在 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;

CTE ranked_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: 部门 ID
name: 部门名称
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;