mysql中WITH的多种用法与常见示例

目录

• 前言
• 1. 非递归 CTE
• 示例 1：计算部门员工的平均工资
• 示例 2：按条件拆分查询
• 2. 递归 CTE
• 示例 3：计算阶乘
• 示例 4：查询部门的层级结构
• 3. 嵌套 CTE 和多 CTE 定义
• 示例 5：多个 CTE 的嵌套查询
• 4. 使用 CTE 简化复杂查询逻辑
• 示例 6：复杂查询的分步计算
• 总结
• 示例：递归查询部门层级
• 递归 CTE 查询：获取指定部门的所有下级部门
• 查询结果解释
• 总结

前言

在 mysql 中，WITH 语句（或称为公用表表达式，Common Table Expressions，简称 CTE）用于定义一个临时结果集，可以在查询的其他部分中重复引用。通常用在复杂查询中，方便将查询逻辑分解为多个部分，代码更清晰，并且可以重复使用中间结果。

MySQL 支持两种类型的 CTE：

• 非递归 CTE：基本的 WITH 语句，用于定义一次性计算的结果集。
• 递归 CTE：CTE 自己引用自己，通常用于分层数据或树状结构的查询。

下面分别介绍它们的用法和一些常见示例。

1. 非递归 CTE

非递归 CTE 在查询中定义一个固定的结果集，在执行后不会再改变。语法如下：

WITH cte_name AS ( SELECT ... ) SELECT * FROM cte_name;

示例 php1：计算部门员工的平均工资

假设有一个 employees 表，包含员工的 department_id、name 和 salary。

WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM empandroidloyees 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.pa编程客栈rent_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.tojstal_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）

表中有五层嵌套的部门数据：

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

执行后，结果显示部门的层级关系：

在这个查询中，level 列表示部门的层级，从1开始递增。

总结

到此这篇关于mysql中WITH的多种用法与常见示例的文章就介绍到这了,更多相关mysql WITH用法示例内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！