mysql WITH的多种用法与示例

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

MySQL 支持两种类型的 CTE：

1. 非递归 CTE：基本的 WITH 语句，用于定义一次性计算的结果集。
2. 递归 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