在上一章，我们见识了子查询的威力，它能像套娃一样构建出复杂的查询。但当嵌套层数过多，或者一个查询中需要多次使用同一个子查询时，代码就会变得像一团乱麻，难以阅读和维护。

为了解决这个问题，SQL 提供了一个超级优雅的工具——通用表表达式 (Common Table Expressions, CTE)。

CTE 可以看作是一个“命了名的临时结果集”，它仅在单个 SQL 语句（SELECT, INSERT, UPDATE, DELETE）的执行范围内存在。你可以用 WITH 关键字来定义一个或多个 CTE，然后像引用普通表一样在主查询中引用它。

使用 CTE 的好处:

• 可读性超强：将复杂的查询逻辑拆分成一步步独立的、命了名的代码块，让查询意图一目了然。
• 可维护性好：修改逻辑时，只需要在对应的 CTE 中修改，而不是深入到复杂的嵌套结构中。
• 可复用性：在同一个查询中，一个 CTE 可以被引用多次。
• 支持递归：这是 CTE 的独门绝技，可以用来处理树状或层级结构的数据，这是普通子查询做不到的。

13.1 使用WITH子句提高查询可读性

我们先来看一个上一章的例子：找出发表文章数量最多的作者的用户名。

用子查询的版本 (回顾):

SELECT
    u.username,
    author_stats.post_count
FROM
    users AS u
JOIN
    (
        SELECT author_id, COUNT(*) AS post_count
        FROM posts
        GROUP BY author_id
    ) AS author_stats ON u.user_id = author_stats.author_id
ORDER BY
    author_stats.post_count DESC
LIMIT 1;

这个查询虽然能工作，但 FROM 子句里嵌着一大块逻辑，不够清爽。

用 CTE 改造后的版本 (见证奇迹的时刻！):

WITH author_stats AS (
    -- 第一步：定义一个名为 author_stats 的 CTE，用来统计每个作者的文章数
    SELECT
        author_id,
        COUNT(*) AS post_count
    FROM
        posts
    GROUP BY
        author_id
)
-- 第二步：在主查询中，像使用一张普通表一样使用 author_stats
SELECT
    u.username,
    s.post_count
FROM
    users AS u
JOIN
    author_stats AS s ON u.user_id = s.author_id
ORDER BY
    s.post_count DESC
LIMIT 1;

代码解析 :

1. WITH author_stats AS (…): WITH 关键字开启了 CTE 的定义。我们创建了一个名为 author_stats 的 CTE，括号里的 SELECT 语句和之前的子查询完全一样。
2. 主查询（SELECT u.username, …）目前变得极其干净。它直接从 users 表和我们刚刚“发明”的 author_stats “表”中查询数据。

整个查询的逻辑被清晰地拆分成了两步，是不是感觉就像在读一篇结构清晰的短文？

定义多个 CTE
你还可以在一个 WITH 子句中，用逗号分隔来定义多个 CTE。后面的 CTE 甚至可以引用在它前面已经定义好的 CTE。

场景：找出平均文章数，并列出所有文章数超过平均值的作者。

WITH
-- CTE 1: 统计每个作者的文章数
author_stats AS (
    SELECT
        author_id,
        COUNT(*) AS post_count
    FROM posts
    GROUP BY author_id
),
-- CTE 2: 计算所有作者的平均文章数 (这里引用了上面的 author_stats)
avg_stats AS (
    SELECT AVG(post_count) AS avg_count FROM author_stats
)
-- 主查询
SELECT
    u.username,
    s.post_count
FROM
    users AS u
JOIN
    author_stats AS s ON u.user_id = s.author_id
WHERE
    s.post_count > (SELECT avg_count FROM avg_stats); -- 这里也可以用 CROSS JOIN

13.2 递归 CTEs (处理层级数据)

这是 CTE 最强劲、最神奇的功能，也是它与普通子查询的根本区别。递归 CTE 能够处理那些具有自我引用关系的层级数据，列如公司的组织架构、产品的分类目录、帖子的评论回复等。

场景设定：我们来创建一个简单的员工表，包含员工 ID、姓名和其直属上级的 ID。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT, -- 指向另一行 employee_id，形成层级关系
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, '大老板', NULL), -- 大老板没有上级
(2, '技术总监', 1),
(3, '市场总监', 1),
(4, '开发组长', 2),
(5, '初级开发', 4),
(6, '市场专员', 3);

问题：我们想查询“初级开发”的所有上级，一直追溯到“大老板”。

使用递归 CTE:

WITH RECURSIVE employee_path AS (
    -- 1. 非递归部分 (起始点、锚点)
    -- 找到我们的出发点：“初级开发”
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = '初级开发'

    UNION ALL

    -- 2. 递归部分
    -- 将上一轮的结果 (employee_path) 与原始表 (employees) 进行 JOIN
    SELECT e.employee_id, e.name, e.manager_id, ep.level + 1
    FROM employees AS e
    JOIN employee_path AS ep ON e.employee_id = ep.manager_id
)
-- 3. 主查询
-- 从最终的递归结果中查询我们想要的数据
SELECT level, name FROM employee_path;

递归过程揭秘 :

1. WITH RECURSIVE: 告知数据库这是一个递归 CTE。
2. 非递归部分 (Anchor Member): 这是递归的起点。它先被执行，找到了“初级开发”这一行，level 为 1。这个结果被放入一个临时的“工作台”上。
3. UNION ALL: 连接非递归和递归部分。
4. 递归部分 (Recursive Member):第一次迭代: 它将“工作台”上的结果（“初级开发”那行）与 employees 表进行 JOIN。连接条件是 e.employee_id = ep.manager_id，也就是 e.employee_id = 4。它找到了“开发组长”这一行，计算出 level 为 2。然后把“开发组长”这一行也放上“工作台”。第二次迭代: 它又将“工作台”上的新结果（“开发组长”那行）与 employees 表 JOIN，找到了“技术总监”，level 为 3。第三次迭代: …找到了“大老板”，level 为 4。第四次迭代: “大老板”的 manager_id 是 NULL，JOIN 不到任何结果，递归结束。
5. 主查询: 最后，employee_path 中包含了从“初级开发”到“大老板”的所有路径上的员工信息，主查询将它们全部取出。

执行结果:

 level |    name
-------+--------------
     1 | 初级开发
     2 | 开发组长
     3 | 技术总监
     4 | 大老板
(4 rows)

是不是超级神奇？我们用纯 SQL 就优雅地解决了这个层级遍历问题！

本章小结

你已经掌握了让 SQL 代码“化繁为简，化腐朽为神奇”的利器！

• 我们学会了使用 WITH 子句将复杂查询拆解成清晰、可读的步骤。
• 了解了如何定义和复用多个 CTE。
• 更重大的是，我们解锁了 RECURSIVE 这个强劲功能，学会了如何处理树状和层级数据。

从今天起，当你再面对一个看起来无比复杂的查询需求时，先别急着写嵌套子查询。静下心来，尝试用 CTE 将它一步步分解。你会发现，再复杂的问题，也能被梳理得井井有条。

在下一章，我们将学习 SQL 中的集合运算，包括 UNION, INTERSECT 和 EXCEPT，它们可以让我们像处理数学集合一样，对查询结果进行合并、取交集和取差集。准备好用新的维度来组合你的数据了吗？我们下一章见！✨