在 SQL 中，WITH 语句用于定义一个或多个公共表表达式（CTE，Common Table Expressions），可以在随后的查询中引用。CTE 提供了一种更清晰和可读的方式来组织复杂的查询，尤其是在需要多次引用相同的子查询时。

基本语法

WITH cte_name AS (
    -- 这里是 CTE 的查询
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

示例

假设我们有一个名为 employees 的表，包含以下字段：id、name、department 和 salary。我们想要查询每个部门的平均薪资，并列出薪资高于平均薪资的员工。

WITH avg_salary AS (
    SELECT department, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, e.department
FROM employees e
JOIN avg_salary a ON e.department = a.department
WHERE e.salary > a.average_salary;

解释

  1. 定义 CTE：在 WITH 子句中，我们定义了一个名为 avg_salary 的 CTE，它计算每个部门的平均薪资。
  2. 使用 CTE：在主查询中，我们从 employees 表中选择员工的姓名、薪资和部门，并通过 JOIN 将其与 avg_salary CTE 连接，以筛选出薪资高于平均薪资的员工。

多个 CTE

你还可以定义多个 CTE，使用逗号分隔：

WITH cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ...
)
SELECT ...
FROM cte1
JOIN cte2 ON ...

递归 CTE

WITH 语句还可以用于递归查询，适用于处理层次结构数据（如组织结构、树形结构等）。

WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归查询
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN cte_name c ON e.manager_id = c.id
)
SELECT *
FROM cte_name;

解释

  1. 基础查询：选择没有经理的员工（顶层）。
  2. 递归查询：通过连接 CTE 自身，查找所有下属员工。

总结

  • WITH 语句用于定义 CTE，使查询更清晰和可读。
  • CTE 可以在查询中多次引用，避免重复代码。
  • 支持递归查询，适合处理层次结构数据。