MySQL中WITH AS语句的使用方法举例例子解析

代码示例：

在MySQL中，WITH 语句（也称为公用表表达式或CTE，Common Table Expressions）允许你在查询中定义临时的结果集，这些结果集可以像常规表一样被引用。WITH 语句通常用于使复杂的查询更易于理解和维护。以下是一些使用 WITH AS 语句的示例：

1. 基本使用

假设我们有一个名为 employees 的表，包含员工信息，我们想要查询工资最高的前5名员工。

WITH TopSalaries AS (
    SELECT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 5
)
SELECT *
FROM employees
WHERE salary IN (SELECT salary FROM TopSalaries);

在这个例子中，TopSalaries 是一个CTE，它首先选出工资最高的5名员工的工资，然后在主查询中使用这个CTE来筛选出这些员工的其他信息。

2. 递归查询

CTE也可以用来执行递归查询，这对于处理层次结构数据非常有用。例如，我们有一个 departments 表，包含部门和它们的上级部门。

WITH RECURSIVE SubordinateDepts AS (
    SELECT department_id, department_name, parent_department_id
    FROM departments
    WHERE department_id = 1 -- 假设1是顶级部门的ID
    UNION ALL
    SELECT d.department_id, d.department_name, d.parent_department_id
    FROM departments d
    INNER JOIN SubordinateDepts sd ON d.parent_department_id = sd.department_id
)
SELECT * FROM SubordinateDepts;

这个递归CTE SubordinateDepts 首先选择了顶级部门，然后递归地选择了所有子部门。

3. 复杂计算

假设我们有一个 sales 表，记录了每个季度的销售数据，我们想要计算每个销售员的年销售总额。

WITH QuarterlySales AS (
    SELECT employee_id, YEAR(sale_date) AS year, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id, YEAR(sale_date)
),
YearlySales AS (
    SELECT employee_id, SUM(total_sales) AS yearly_total
    FROM QuarterlySales
    GROUP BY employee_id
)
SELECT *
FROM YearlySales;

在这个例子中，QuarterlySales CTE 计算了每个员工每个季度的销售总额，然后 YearlySales CTE 计算了每个员工的年销售总额。

4. 条件过滤

我们可以使用CTE来简化复杂的查询，特别是当涉及到多步过滤时。

WITH FilteredEmployees AS (
    SELECT *
    FROM employees
    WHERE department_id = 3 -- 假设我们只关心特定部门的员工
)
SELECT *
FROM FilteredEmployees
WHERE salary > 50000;

这里，FilteredEmployees CTE 首先过滤出特定部门的员工，然后在主查询中进一步过滤出工资高于50000的员工。

注意事项

• CTE可以在 SELECT, INSERT, UPDATE, 或 DELETE 语句中使用。
• CTE定义的结果集在主查询中可以被多次引用。
• CTE可以是递归的，也可以是非递归的。

使用CTE可以使复杂的SQL查询更加清晰和易于管理。

喜欢本文，请点赞、收藏和关注！