请求清晰简洁地讲解SQL中的CTE（公共表表达式）

什么是CTE？ #

CTE（Common Table Expression，公共表表达式）是SQL中一种临时、一次性的结果集定义，仅在当前SQL语句执行期间有效，核心作用是简化复杂查询逻辑，让代码更易读、更易维护。

核心特点 #

• 仅在当前SQL语句的生命周期内存在，执行完毕后自动销毁，不会持久化存储到数据库
• 支持自引用（即递归CTE），适合处理层级或递归结构的数据
• 相比嵌套子查询，结构更清晰，多层复杂查询时优势明显
• 可在SELECT、INSERT、UPDATE、DELETE等多种语句中使用

基础语法 #

WITH cte_name (列1, 列2, ...) AS (
    -- 定义CTE的查询语句，返回的结果集就是cte_name代表的临时表
    SELECT 列1, 列2 FROM 目标表 WHERE 筛选条件
)
-- 后续查询可直接像使用普通表一样调用cte_name
SELECT * FROM cte_name;

简单实用示例 #

假设存在orders表，包含order_id、user_id、order_amount字段，要筛选出订单总金额超过1000的用户，用CTE实现如下：

WITH 用户订单总额 AS (
    SELECT user_id, SUM(order_amount) AS 总金额
    FROM orders
    GROUP BY user_id
)
SELECT user_id, 总金额
FROM 用户订单总额
WHERE 总金额 > 1000;

这里CTE先计算出每个用户的订单总额，主查询直接基于这个临时结果集做筛选，逻辑比嵌套子查询更直观。

递归CTE基础说明 #

CTE支持递归调用自身，主要用于处理层级数据（比如部门组织结构、评论回复链）。基础结构是通过UNION ALL连接初始查询（终止条件）和递归查询（引用自身）。

内容的提问来源于stack exchange，提问作者Ashish