MySQL 中 with...as 如何使用

在 MySQL 数据库中，WITH...AS 子句是一个强大且实用的功能，也被称为公共表表达式（CTE）。它允许你在 SELECT、INSERT、UPDATE 或 DELETE 语句中定义一个临时结果集，这个临时结果集可以在主查询中多次引用，从而增强了查询的可读性和可维护性。

我们来看一下 WITH...AS 的基本语法：

WITH common_table_expression AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM common_table_expression;

在上述语法中，common_table_expression 是公共表表达式的名称，你可以自定义。括号内的 SELECT 语句用于定义这个临时结果集。然后在主查询中就可以像使用普通表一样引用这个公共表表达式。

例如，我们有一个 employees 表，包含员工的 id、name、salary 和 department_id 等信息。现在我们想找出每个部门中薪资高于该部门平均薪资的员工。使用 WITH...AS 可以这样写：

WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, das.avg_salary
FROM employees e
JOIN dept_avg_salary das ON e.department_id = das.department_id
WHERE e.salary > das.avg_salary;

在这个例子中，dept_avg_salary 公共表表达式计算出了每个部门的平均薪资。然后在主查询中，我们将 employees 表与这个临时结果集进行连接，筛选出薪资高于平均薪资的员工。

WITH...AS 还支持递归查询。比如我们有一个表示公司组织结构的表 organization，包含 employee_id 和 manager_id 字段。要查询某个员工及其所有下属，可以这样写：

WITH RECURSIVE employee_subordinates AS (
    SELECT employee_id, manager_id
    FROM organization
    WHERE employee_id = 1 -- 以员工 1 为例
    UNION ALL
    SELECT o.employee_id, o.manager_id
    FROM organization o
    INNER JOIN employee_subordinates es ON o.manager_id = es.employee_id
)
SELECT employee_id
FROM employee_subordinates;

这里通过 RECURSIVE 关键字启用递归，UNION ALL 将初始查询结果与递归部分的结果合并。

MySQL 的 WITH...AS 子句为复杂查询提供了更清晰的结构，合理运用它能有效提升数据库查询的效率和代码的可读性，让开发者更轻松地处理复杂的数据检索和分析任务。