mysql WITH的多种用法与示例

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

1. 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 举行暂时排名：

1. 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 允许在界说时引用自身，常用于层级结构的查询，比如管理层次结构、树形结构等。语法如下：

1. WITH RECURSIVE cte_name AS ( SELECT ... -- 初始查询 UNION ALL SELECT ... FROM cte_name -- 递归查询 ) SELECT * FROM cte_name;

复制代码

示例 3：计算阶乘

下面是一个递归 CTE 示例，计算 1 到 5 的阶乘。

1. 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 可以查询从某个部门开始的所有子部门。

1. 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：

1. 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：复杂查询的分步计算

假设有一个电商订单系统，要求统计每月每个产品的贩卖额及增长率。

1. 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）
  

表中有五层嵌套的部门数据：
idnamepid1公司NULL2技能部13市场部14开辟组25测试组26前端开辟47后端开辟48大客户市场部39中小客户市场部3 递归 CTE 查询：获取指定部门的所有下级部门

我们可以使用递归 CTE 从根部门（例如公司层级的 id=1）开始，查询所有子部门并表现层级关系。

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，这样层级关系会递归增长，直到没有下级部门。
  

实行后，结果表现部门的层级关系：
idnamepidlevel1公司NULL12技能部123市场部124开辟组235测试组236前端开辟447后端开辟448大客户市场部339中小客户市场部33 在这个查询中，level 列表现部门的层级，从1开始递增。



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。