sql-common-table-expression

SQL - 通用表表达式（CTE）

通用表表达式（CTE）是编写更简洁、更具可读性和更易于维护的 SQL 的强大功能。本章将介绍 CTE、使用 WITH 子句的语法，以及如何将它们用于简单和复杂的递归查询。

什么是通用表表达式？

CTE 是一个命名的临时结果集，您可以在单个 SELECT、INSERT、UPDATE 或 DELETE 语句中引用它。可以将其视为创建了一个临时的、一次性使用的视图。CTE 有助于将冗长复杂的查询分解为逻辑上可重用的构建块，从而显著提高可读性。

CTE 使用 WITH 子句定义。此功能是 SQL 标准的一部分，并受到所有现代关系数据库管理系统（RDBMS）的支持，包括 PostgreSQL、SQL Server、Oracle、SQLite 和 MySQL（8.0 及更高版本）。

语法

单个 CTE 的基本语法如下：

WITH CteName (column1, column2, ...) AS (
    -- 定义 CTE 的查询
    SELECT ...
    FROM ...
)
-- 使用 CTE 的主查询
SELECT *
FROM CteName;

其中：

• CteName：您为临时结果集指定的有意义的名称。
• (column1, ...)：CTE 的可选列名列表。如果省略，则使用 CTE 的 SELECT 语句中的列。
• AS (...)：括号中包含生成临时结果集的查询。
• 主查询：可以像引用常规表一样引用 CteName 的最终查询。

简单 CTE 和链式 CTE

让我们使用一个示例 employees 表来实际操作 CTE。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 120000, 3),
(2, 'Bob', 'Engineering', 100000, 1),
(3, 'Charlie', 'Management', 150000, NULL),
(4, 'David', 'Sales', 90000, 5),
(5, 'Eve', 'Sales', 110000, 3);

示例：单个 CTE

查找“Engineering”部门的所有员工。

WITH EngineeringDept AS (
    SELECT id, name, salary
    FROM employees
    WHERE department = 'Engineering'
)
SELECT name, salary
FROM EngineeringDept
ORDER BY salary DESC;

示例：链式多个 CTE

您可以在单个 WITH 子句中定义多个 CTE，用逗号分隔。后面的 CTE 甚至可以引用前面的 CTE。让我们找出销售部门的总薪资，然后找出所有薪资高于销售部门平均薪资的员工。

WITH
  SalesDept AS (
    -- 第一个 CTE：筛选销售部门员工
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
  ),
  SalesStats AS (
    -- 第二个 CTE：计算平均薪资，引用第一个 CTE
    SELECT AVG(salary) as avg_sales_salary
    FROM SalesDept
  )
-- 主查询：使用计算出的平均薪资来查找高收入员工
SELECT e.name, e.department, e.salary
FROM employees e, SalesStats ss
WHERE e.salary > ss.avg_sales_salary;

递归 CTE

递归 CTE 是指引用自身的 CTE。这对于查询层次数据（如组织结构图、文件系统或物料清单）非常有用。

递归 CTE 包含两部分：

• 锚定成员： 返回基础结果集的初始查询（例如，公司的 CEO）。
• 递归成员： 引用 CTE 本身并与锚定成员连接的查询。它会重复执行，直到不再返回行。
• 这两个成员通过 UNION ALL 组合在一起。

示例：员工层级结构

让我们找出“Alice”（员工 ID 为 1）的完整报告链。

WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚定成员：从顶级经理 Charlie 开始
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 3 -- Charlie is the starting point (CEO/top manager)

    UNION ALL

    -- 递归成员：查找向上一级报告的员工
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level;

输出

ID	姓名	经理ID	层级
3	Charlie	null	0
1	Alice	3	1
5	Eve	3	1
2	Bob	1	2

CTE 的优缺点

优点

• 可读性： 将复杂的逻辑分解为易于理解的步骤。
• 可维护性： 更易于调试和修改单个逻辑单元。
• 递归： 提供了一种优雅的方式来查询层次数据，这在传统连接中很难实现。
• 可重用性： CTE 可以在主查询中多次引用，避免冗余代码。

缺点

• 作用域： CTE 仅对其紧随其后的语句可见。
• 性能： 尽管通常情况下清晰明了，但与子查询或临时表相比，深度嵌套或复杂的 CTE 有时会使查询优化器更难找到最佳执行计划。对于关键查询，务必进行性能分析。