摘要：在上一章，我们见识了子查询的威力，它能像套娃一样构建出复杂的查询。但当嵌套层数过多，或者一个查询中需要多次使用同一个子查询时，代码就会变得像一团乱麻，难以阅读和维护。
在上一章，我们见识了子查询的威力，它能像套娃一样构建出复杂的查询。但当嵌套层数过多，或者一个查询中需要多次使用同一个子查询时，代码就会变得像一团乱麻，难以阅读和维护。
为了解决这个问题，SQL 提供了一个超级优雅的工具——通用表表达式 (Common Table Expressions, CTE)。
CTE 可以看作是一个“命了名的临时结果集”，它仅在单个 SQL 语句（SELECT, INSERT, UPDATE, DELETE）的执行范围内存在。你可以用 WITH 关键字来定义一个或多个 CTE，然后像引用普通表一样在主查询中引用它。
使用 CTE 的好处:
可读性超强：将复杂的查询逻辑拆分成一步步独立的、命了名的代码块，让查询意图一目了然。可维护性好：修改逻辑时，只需要在对应的 CTE 中修改，而不是深入到复杂的嵌套结构中。可复用性：在同一个查询中，一个 CTE 可以被引用多次。支持递归：这是 CTE 的独门绝技，可以用来处理树状或层级结构的数据，这是普通子查询做不到的。我们先来看一个上一章的例子：找出发表文章数量最多的作者的用户名。
用子查询的版本 (回顾):
SELECT u.username, author_stats.post_countFROM users AS uJOIN ( SELECT author_id, COUNT(*) AS post_count FROM posts GROUP BY author_id ) AS author_stats ON u.user_id = author_stats.author_idORDER BY author_stats.post_count DESCLIMIT 1;这个查询虽然能工作，但 FROM 子句里嵌着一大块逻辑，不够清爽。
用 CTE 改造后的版本 (见证奇迹的时刻！):
WITH author_stats AS ( -- 第一步：定义一个名为 author_stats 的 CTE，用来统计每个作者的文章数 SELECT author_id, COUNT(*) AS post_count FROM posts GROUP BY author_id)-- 第二步：在主查询中，像使用一张普通表一样使用 author_statsSELECT u.username, s.post_countFROM users AS uJOIN author_stats AS s ON u.user_id = s.author_idORDER BY s.post_count DESCLIMIT 1;代码解析 :
WITH author_stats AS (...): WITH 关键字开启了 CTE 的定义。我们创建了一个名为 author_stats 的 CTE，括号里的 SELECT 语句和之前的子查询完全一样。主查询（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_countFROM users AS uJOIN author_stats AS s ON u.user_id = s.author_idWHERE s.post_count > (SELECT avg_count FROM avg_stats); -- 这里也可以用 CROSS JOIN这是 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;递归过程揭秘 :
WITH RECURSIVE: 告诉数据库这是一个递归 CTE。非递归部分 (Anchor Member): 这是递归的起点。它先被执行，找到了“初级开发”这一行，level 为 1。这个结果被放入一个临时的“工作台”上。UNION ALL: 连接非递归和递归部分。递归部分 (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 不到任何结果，递归结束。主查询: 最后，employee_path 中包含了从“初级开发”到“大老板”的所有路径上的员工信息，主查询将它们全部取出。执行结果:
level | name-------+ 1 | 初级开发 2 | 开发组长 3 | 技术总监 4 | 大老板(4 rows)是不是非常神奇？我们用纯 SQL 就优雅地解决了这个层级遍历问题！
本章小结
你已经掌握了让 SQL 代码“化繁为简，化腐朽为神奇”的利器！
我们学会了使用 WITH 子句将复杂查询拆解成清晰、可读的步骤。了解了如何定义和复用多个 CTE。更重要的是，我们解锁了 RECURSIVE 这个强大功能，学会了如何处理树状和层级数据。从今天起，当你再面对一个看起来无比复杂的查询需求时，先别急着写嵌套子查询。静下心来，尝试用 CTE 将它一步步分解。你会发现，再复杂的问题，也能被梳理得井井有条。
在下一章，我们将学习 SQL 中的集合运算，包括 UNION, INTERSECT 和 EXCEPT，它们可以让我们像处理数学集合一样，对查询结果进行合并、取交集和取差集。准备好用新的维度来组合你的数据了吗？我们下一章见！✨
来源：linux运维菜
