InkSpace

Menu

LuInk
Published on 2025-07-01 / 21 Visits
0
0

【SQL进阶】用 CTE 把复杂逻辑 “拆” 简单

#sql进阶

引言​

大家好！在写SQL时，特别是涉及多层逻辑关联或复杂计算时，大家是不是经常遇到这种情况：写了一个超长的查询，里面塞满了各种子查询，一层又一层地嵌套，自己写的时候可能还能理清，过几天再看或者让别人维护时，简直像看天书？更别提想要复用中间结果时的麻烦了。本文就给大家介绍一个SQL中的强大帮手——CTE（Common Table Expression，公共表表达式）​。它就是一个 “临时结果集”，能在一次查询中被反复引用，能帮你理清思路、简化代码，特别是在处理树状结构数据（比如组织架构、多级菜单）时，更是神！

为什么你需要CTE？主要优势​

  1. ​提高可读性与可维护性：​​ 这是CTE最大的好处！它将复杂的子查询逻辑抽取出来，并赋予一个有意义的别名，放在查询语句的最前面。这样，主查询就能变得非常简洁清晰，只关注最终需要的组合关系，逻辑一目了然。再也不用在括号的海洋里迷失方向了。

  2. ​避免重复：​​ 如果一个子查询需要在主查询中被引用多次（例如用于连接或过滤），你需要在多处重复写它。使用CTE定义一次后，主查询中可以多次引用这个CTE名，避免了代码冗余和潜在的失误。

  3. ​支持递归（超级强大！）：​​ CTE有一种特殊的模式，叫递归CTE (Recursive CTE)​，这是它独一无二的杀手锏。它能够查询出具有树形结构（层级结构）​的数据，例如部门与子部门、多级分类、文件夹结构、评论回复树等等。没有递归CTE之前，处理这种数据通常非常繁琐甚至需要借助存储过程/函数。现在，一个优雅的递归CTE就能搞定！

CTE的语法

WITH [RECURSIVE] cte_name AS (
    -- 基础查询（对于递归CTE，这是非递归部分）
    SELECT 
      column_name1,
      column_name2,
      ...
    FROM ...
    WHERE ...
    [UNION | UNION ALL] -- 对于递归CTE，这里连接递归部分
    -- 递归部分（可选，仅在RECURSIVE时用）
    SELECT .
      column_name1,
      column_name2,
      ...
    FROM cte_name -- 关键：引用自身!
    JOIN ...
    WHERE ...
)
-- 主查询，使用上面定义的CTE(s)
SELECT 
  column_name1,
  column_name2,
  ...
FROM cte_name
[WHERE ...]
[ORDER BY ...];

  • WITH 关键字开始CTE的定义。

  • RECURSIVE 修饰符表示这是一个递归CTE（可选）。

  • cte_name 是你给这个临时结果集起的名字。

  • AS ( ... ) 内部定义了这个CTE的结果集的查询逻辑。

  • 定义完CTE后，紧跟着主查询（SELECT, INSERT等），主查询中可以引用这些CTE。

举个栗子​​

​栗子1：简单CTE - 提高可读性 & 复用性​
假设你有一个orders订单表和customers客户表。你想找出总订单金额超过1000的客户，并列出他们的名字和总金额。​没有CTE时，你可能会写一个包含子查询的语句：

SELECT c.customer_name, t.total_amount
FROM customers c
JOIN (
    SELECT customer_id, SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amount) > 1000
) AS t ON c.customer_id = t.customer_id;

​使用CTE重写：​​

WITH CustomerTotal AS (
    SELECT customer_id, SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amount) > 1000
)
SELECT c.customer_name, ct.total_amount
FROM customers c
JOIN CustomerTotal ct ON c.customer_id = ct.customer_id;

​优势体现：​​

  • 主查询变得极其简洁，只负责customers表和CTE结果的自然连接。

  • CustomerTotal CTE 清晰定义了“总金额>1000的客户ID及其总金额”这个中间结果。

  • 如果后续还需要基于CustomerTotal做其他操作（比如再次筛选特定总金额范围的客户），只需要在WITH子句后继续引用它，非常方便复用。

​栗子2：递归CTE - 征服树形结构​
假设你有一个departments表存储部门结构，它包含字段：
id (部门ID)
name (部门名称)
parent_id (父部门ID, NULL表示顶级部门)

你想查出某个指定顶级部门（比如ID=1）下所有层级的子部门（包括它自己）的信息。

​使用递归CTE：​

WITH RECURSIVE DepartmentTree AS (
    -- 基础部分（Anchor Member）：定位起始点（顶级部门）
    SELECT id, name, parent_id, 1 AS level -- 初始层级设为1
    FROM departments
    WHERE id = 1 -- 假设顶级部门的ID是1
    UNION ALL
    -- 递归部分（Recursive Member）：连接子部门
    SELECT d.id, d.name, d.parent_id, dt.level + 1 -- 层级递增
    FROM departments d
    INNER JOIN DepartmentTree dt ON d.parent_id = dt.id -- 关键：关联CTE自身，找dt的子部门
)
SELECT * FROM DepartmentTree;

​关键解析：​​

  • ​WITH RECURSIVE:​​ 声明这是一个递归CTE。

  • ​基础部分 (SELECT ... WHERE id = 1)：​​ 这是我们递归树的起点（根节点）。它查询出我们关心的顶级部门（ID=1）的信息，并初始化一个level字段表示当前层级为1。

  • ​递归部分 (SELECT ... INNER JOIN DepartmentTree ...)：​​

    • 它连接departments表 (d) 和 DepartmentTree ​自身​ (dt)。

    • 条件是d.parent_id = dt.id：这意味着我们在找DepartmentTree当前结果集中部门的所有直属子部门​ (d)。

    • 在每次递归迭代中，dt.level + 1 表示新找到的子部门比父部门(dt)的层级深一级。

  • ​UNION ALL：​​ 将基础部分的结果和递归部分找到的新结果合并在一起（保留所有行）。

  • ​终止条件：​​ 递归过程会一直执行，直到递归部分没有再连接到任何新的记录（即找不到新的子部门）为止。

  • ​最终结果 (SELECT * FROM DepartmentTree)：​​ 返回的就是包含ID=1部门及其所有子孙部门的完整层级列表，并且level字段清晰地标识了它们在树中的深度（根=1，其子=2，孙子=3，等等）。

​兼容性说明​

如开头所说，CTE是现代SQL的一个标准特性：

  • ​PostgreSQL:​​ 很早就支持了，非常好用。

  • ​MySQL:​​ 这属于MySQL8.0版本的新特性，已经全面支持标准CTE（包括递归）。

  • ​SQL Server, Oracle, SQLite 等：​​ 也都有良好的支持。

  • ​重要提示：​​ 如果你使用的是MySQL ​5.7​ 或更早版本，则无法使用CTE，只能继续忍受子查询嵌套了😅。

​实用小贴士​

  1. ​先思考再写CTE：​​ 在写复杂SQL前，先在脑子里理清主要步骤。CTE是为了体现这些清晰步骤而生的。

  2. ​命名要有意义：​​ CTE的名字（如CustomerTotal, DepartmentTree, MonthlySalesSummary）应该清晰反映它所代表的数据内容。避免用temp1, a1这种无意义的命名。

  3. ​小心递归：​​ 递归CTE非常强大，但要确保你的递归部分有明确的终止条件（例如，parent_id IS NULL作为起点，自然递归到没有子部门停止）。设计不当的递归可能导致无限循环（数据库通常有最大递归深度限制来防止这种情况）。

  4. ​性能考量：​​ CTE通常不会自动创建物理临时表（除非优化器特殊处理或显式指定），它更像是查询重写。对于复杂查询，合理使用CTE通常比深层次嵌套子查询更容易让优化器理解和优化，有时性能更好。但在一些情况下（如MySQL早期版本或特定复杂递归），也可能有细微差别，在特别关注性能的极限场景下可以对比分析执行计划。不过对于可读性和维护性的巨大提升而言，CTE通常是值得的。MySQL 8的窗口函数和CTE一起用更是如虎添翼。

​结语​

CTE，尤其是递归CTE，是构建清晰、模块化且强大SQL查询的必备技能。它把我们从层层嵌套的子查询“地狱”中解救出来，让SQL逻辑变得像搭积木一样清晰可见。特别是处理无限层级的树状数据时，递归CTE几乎是目前SQL中最优雅高效的解决方案。

【新点子】开启SQL进阶计划

Comment