五分钟了解数据库复杂查询SQL神器 - CTE复杂SQL写到手软？一堆子查询，逻辑不清晰！不会递归,还在parent_i

关于 CTE

什么是CTE?

CTE(Common Table Expressions)是一种SQL标准，对于我们来说就是一套语法，提供的是 “命名的临时结果集（named temporary result set）” ，用于提高 SQL 的可读性、可维护性，以及支持递归查询。

CTE解决了什么问题？

我觉得有两点，一是个让你写SQL的时候能存临时变量，二是处理递归。第一个帮你省事，第二个做到SQL以前做不到的事。

CTE的临时变量

写SQL的一个痛点就是没有地方存存临时变量。比如你要写一个很复杂的查询，其中有一个平均值，而这个平均值是一个子查询SQL（假如还超复杂有几十行SQL），而你要还要用很多次，那么每次用的时候你都要复制这个子查询SQL，是不是很麻烦。那么现在有了CTE，你可以直接把这个平均值SQL （以表的形式）当做一个临时变量，像是 common_avg_value_table,后面只需调用common_avg_value_table.value就能拿到值，是不是很方便。
如此一来你写SQL的时候可以转变观念，没有CTE的时候，你的SQL开上去是层层嵌套的紧密结构，有了CTE则可以使用模块化的思维，像变形金刚合体一样，每次处理一部分，最后汇总结果。

CTE的变量不是真本量

虽然我说CTE能够存临时变量，但更像是一种不太严谨的类比，更严谨的说法是“命名的临时结果集”，我举个例子你就明白了。

假如一个报表中有10个地方都用到了一个 CTE 变量avg_table.value，这个avg_table里面就是一个类似于select avg(xxx) as value from xxx的查询，这个查询一次花1秒，你报表中调用10次，这个查询就会执行10次，也就用了10秒在这上面。

这种调用特性其实不是变量，更像是引用或者说函数。这在大数据量 / 性能敏感 SQL 情况下需要注意。

当然如果你想做到一次查询，多次复用的功能，MySQL 8.0.31+ 提供了一种“物化（MATERIALIZED）”的语法，能实现这一点，感兴趣的可以了解，但要注意物化 ≠ 一定更快,否则官方默认就支持了，所以还是了解清楚再考虑使用吧。

CTE使用示例

假设你要算一个复杂的报表： “找出今年每个月销售额超过平均值的部门” 。

-- 传统做法（子查询嵌套）：
SELECT department_id, monthly_sales
FROM 
(SELECT department_id, SUM(amount) as monthly_sales FROM sales GROUP BY department_id)
as s
WHERE monthly_sales > 
(
  SELECT AVG(total_sales) FROM 
   (
   SELECT SUM(amount) as total_sales FROM sales GROUP BY department_id
   ) as t
);


-- CTE 做法（模块化）
WITH DeptSales AS (
    -- 模块1：计算每个部门的销售额
    SELECT department_id, SUM(amount) AS monthly_sales
    FROM sales
    GROUP BY department_id
),
AvgSales AS (
    -- 模块2：基于模块1计算全局平均值
    SELECT AVG(monthly_sales) AS global_avg 
    FROM DeptSales
)
-- 模块3：最终过滤输出
SELECT * FROM DeptSales 
WHERE monthly_sales > (SELECT global_avg FROM AvgSales);

Recursive CTE （递归 CTE）

数据库与递归

标准的 SQL 语言最初是基于关系代数设计的，其核心是“集合论”。集合论擅长处理横向的过滤和合并，但天生不擅长处理纵向的、具有依赖关系的迭代逻辑（即递归）。

所以以前数据库处理递归还挺有意思的：

用存储过程，直接编程干
加字段，没有什么是加一个字段解决不了的问题，如果有那就加两个，比如path，存储像是1/5/12/30这样的字符，查询就where path like '1/5/%'一把抓出所有下属。
数据库的私有语法：在CTE成为SQL标准前，Oracle就使用connect by prior处理递归。

递归 CTE 介绍

前面说了数据库处理递归不方便，所以就出了一套专门处理递归的 SQL 规范，Recursive CTE,它的出现本质上是把“有限递归”纳入声明式查询模型中。
需注意MySQL 递归 CTE 有限制，默认最大递归深度：cte_max_recursion_depth（默认 1000），这个你可以改

CTE递归示例

-- 模板
WITH RECURSIVE cte_name AS (
    -- 1. 初始成员 (Anchor Member): 递归的起点/基准情形
    SELECT ... 
    
    UNION [ALL]
    
    -- 2. 递归成员 (Recursive Member): 定义“如何从上一步推导到下一步”
    SELECT ... FROM cte_name INNER JOIN table ON ...
)
-- 3. 外部查询: 最终结果
SELECT * FROM cte_name;

-- 示例
WITH RECURSIVE calendar AS (
    SELECT '2024-01-01' AS date_day -- 起点
    UNION ALL
    SELECT date_add(date_day, INTERVAL 1 DAY) -- 迭代规则：加一天
    FROM calendar
    WHERE date_day < '2024-01-10' -- 终止条件
)
SELECT * FROM calendar;

CTE 总结一下

CTE提供了命名结果集功能，为模块化SQL建立了基础。
RECURSIVE CTE 把“有限递归”纳入声明式查询模型中，从此递归SQL也能上桌吃饭了。
CTE 也不是万能灵药，可能的性能问题要注意。