CTE与临时表

Q: Q：递归 CTE 会无限循环吗？

A： 如果没有正确的终止条件，会的。确保递归部分有 WHERE 条件限制深度或范围。MySQL 有 cte_max_recursion_depth 参数（默认1000）防止无限递归。

Q: Q：多个 CTE 之间能互相引用吗？

A： 可以。在同一个 WITH 子句中定义的多个 CTE，后面的可以引用前面的。但不能交叉引用（CTE A 引用 CTE B，同时 CTE B 又引用 CTE A）。

🌍 生活类比

想象你在做一道复杂的菜：

子查询 — 每次用到"切好的葱花"，都要重新切一次（重复执行）
CTE — 先把葱花切好放在碗里，后面随时取用（命名一次，多次引用）
临时表 — 把切好的配料放在临时盘子里，不仅这道菜能用，整个烹饪过程中都能用（会话内可反复访问）

CTE 和临时表都是"先准备、后使用"的思路，让复杂查询变得清晰有序。

🎯 核心概念

WITH 子句 / CTE

CTE（Common Table Expression，公共表表达式）用 WITH 关键字定义一个临时命名结果集，在当前查询中可以像表一样引用。

SQL

WITH cte_name AS (
    -- CTE 查询
    SELECT column1, column2 FROM table1
)
-- 主查询引用 CTE
SELECT * FROM cte_name;

优势：

可读性：将复杂查询拆分为多个逻辑步骤
可复用：同一 CTE 可以在主查询中多次引用
可递归：支持树形结构遍历

递归 CTE

递归 CTE 用 WITH RECURSIVE 定义，包含两部分：

锚点成员（Anchor）：初始查询，递归的起点
递归成员（Recursive）：引用自身，逐步扩展

SQL

WITH RECURSIVE cte_name AS (
    -- 锚点：初始查询
    SELECT ... FROM table WHERE 条件
    UNION ALL
    -- 递归：引用自身
    SELECT ... FROM table JOIN cte_name ON 条件
)
SELECT * FROM cte_name;

适用场景：组织架构树、分类目录、路径查找等层级数据。

CTE vs 子查询

特性	CTE	子查询
可读性	更好（先命名再使用）	嵌套深时难读
复用性	多次引用	每次引用重新执行
递归	✅ 支持	❌ 不支持
性能	通常被优化为相同计划	相同
作用域	仅当前语句	仅当前语句

💡 经验法则：查询逻辑复杂、需要多次引用同一结果集时，优先用 CTE。

临时表 — CREATE TEMPORARY TABLE

临时表存在于当前数据库会话中，会话结束自动删除。

SQL

CREATE TEMPORARY TABLE temp_result (
    id INT,
    name VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO temp_result SELECT ...;
SELECT * FROM temp_result;

适用场景：

多条 SQL 共享同一中间结果
存储过程中的中间数据
需要对中间结果创建索引

CTE 适用场景总结

场景	推荐方案
简单查询中的中间结果	子查询
复杂查询拆分步骤	CTE
树形/层级数据遍历	递归 CTE
跨多条 SQL 共享数据	临时表
中间结果需要索引	临时表

WITH RECURSIVE 遍历树形数据

员工表中的 department_id 或自引用的 manager_id 字段可以形成层级关系，递归 CTE 能轻松遍历：

SQL

-- 找出某员工的所有上级（向上递归）
-- 找出某部门下的所有子部门（向下递归）
-- 生成日期序列（数字序列）

📝 基本语法

SQL

-- 基本 CTE
WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

-- 多个 CTE
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ... FROM cte1 JOIN cte2 ON ...;

-- 递归 CTE
WITH RECURSIVE cte_name AS (
    -- 锚点（起点）
    SELECT ... FROM table WHERE 初始条件
    UNION ALL
    -- 递归（引用自身）
    SELECT ... FROM table 
    JOIN cte_name ON 关联条件
    WHERE 终止条件
)
SELECT * FROM cte_name;

-- 临时表
CREATE TEMPORARY TABLE temp_name AS
SELECT ... FROM ...;

-- 或手动创建
CREATE TEMPORARY TABLE temp_name (
    col1 INT,
    col2 VARCHAR(50)
);

💡 提示：

CTE 只能在紧随其后的单条 SELECT/INSERT/UPDATE/DELETE 中使用

递归 CTE 必须有终止条件，否则会无限循环

临时表名不能与已有表同名

MySQL 使用 CREATE TEMPORARY TABLE，SQL Server 使用 #temp_table

📌 示例

示例：用 CTE 拆分复杂查询 — 部门人均销售额分析

SQL

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name AS 部门,
        COUNT(DISTINCT e.employee_id) AS 员工数,
        COUNT(o.order_id) AS 订单数,
        COALESCE(SUM(o.total_amount), 0) AS 总销售额
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY d.department_id, d.department_name
)
SELECT 
    部门,
    员工数,
    订单数,
    总销售额,
    CASE WHEN 员工数 > 0 THEN ROUND(总销售额 / 员工数, 2) ELSE 0 END AS 人均销售额,
    CASE WHEN 员工数 > 0 THEN ROUND(订单数 * 1.0 / 员工数, 2) ELSE 0 END AS 人均订单数
FROM dept_stats
ORDER BY 人均销售额 DESC;

▶ 试一试

执行结果：

TEXT

部门   | 员工数 | 订单数 | 总销售额  | 人均销售额 | 人均订单数
------+--------+--------+---------+-----------+----------
销售部 |      2 |      4 | 15100.00|   7550.00 |      2.00
技术部 |      2 |      3 |  8750.00|   4375.00 |      1.50
市场部 |      1 |      0 |     0.00|      0.00 |      0.00
财务部 |      0 |      0 |     0.00|      0.00 |      0.00

说明：dept_stats CTE 先汇总基础数据，主查询再计算人均指标。逻辑清晰，易于维护。

示例：递归 CTE — 员工管理层级

假设 employees 表有一个 manager_id 字段表示直属上级：

SQL

-- 先添加 manager_id 字段和数据
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = NULL WHERE employee_id = 1;  -- 张伟：总经理
UPDATE employees SET manager_id = 1    WHERE employee_id = 2;  -- 李娜 → 张伟
UPDATE employees SET manager_id = 1    WHERE employee_id = 3;  -- 王强 → 张伟
UPDATE employees SET manager_id = 3    WHERE employee_id = 4;  -- 赵敏 → 王强
UPDATE employees SET manager_id = 3    WHERE employee_id = 5;  -- 刘洋 → 王强
UPDATE employees SET manager_id = NULL WHERE employee_id = 6;  -- 陈静：无上级

-- 递归查询：从张伟开始，找出所有下属（向下遍历）
WITH RECURSIVE emp_hierarchy AS (
    -- 锚点：起点（张伟）
    SELECT 
        employee_id,
        CONCAT(first_name, last_name) AS 姓名,
        manager_id,
        0 AS 层级,
        CAST(CONCAT(first_name, last_name) AS CHAR(500)) AS 路径
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：找下级
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name),
        e.manager_id,
        h.层级 + 1,
        CAST(CONCAT(h.路径, ' → ', e.first_name, e.last_name) AS CHAR(500))
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', 层级), 姓名) AS 组织架构,
    层级,
    路径
FROM emp_hierarchy
ORDER BY 路径;

▶ 试一试

执行结果：

TEXT

组织架构       | 层级 | 路径
-------------+------+---------------------------
张伟          |    0 | 张伟
  李娜        |    1 | 张伟 → 李娜
  王强        |    1 | 张伟 → 王强
    赵敏      |    2 | 张伟 → 王强 → 赵敏
    刘洋      |    2 | 张伟 → 王强 → 刘洋
陈静          |    0 | 陈静

说明：

锚点成员找出所有"无上级"的顶层员工
递归成员逐层查找下属，层级 + 1 记录深度
REPEAT(' ', 层级) 用缩进直观展示层级关系
CAST(... AS CHAR(500)) 防止递归时字符串截断

🎬 场景演练

场景一：分步骤计算客户生命周期价值

SQL

WITH order_summary AS (
    -- 第一步：汇总每个客户的订单数据
    SELECT 
        customer_id,
        MIN(order_date) AS 首次购买,
        MAX(order_date) AS 最近购买,
        COUNT(order_id) AS 订单数,
        SUM(total_amount) AS 总消费
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_ltv AS (
    -- 第二步：计算生命周期天数和消费频率
    SELECT 
        customer_id,
        首次购买,
        最近购买,
        订单数,
        总消费,
        DATEDIFF(最近购买, 首次购买) AS 生命周期天数,
        CASE 
            WHEN DATEDIFF(最近购买, 首次购买) > 0 
            THEN ROUND(总消费 / (DATEDIFF(最近购买, 首次购买) / 30.0), 2)
            ELSE 总消费
        END AS 月均消费
    FROM order_summary
)
SELECT 
    customer_id AS 客户ID,
    首次购买,
    最近购买,
    订单数,
    总消费,
    生命周期天数,
    月均消费,
    CASE
        WHEN 月均消费 >= 3000 THEN '高价值'
        WHEN 月均消费 >= 1000 THEN '中价值'
        ELSE '低价值'
    END AS 客户等级
FROM customer_ltv
ORDER BY 总消费 DESC;

要点：多个 CTE 串联，每一步逻辑清晰——先汇总、再计算、最后分级。比嵌套子查询易读得多。

场景二：生成日期序列用于销售趋势分析

SQL

WITH RECURSIVE date_series AS (
    -- 锚点：起始日期
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    -- 递归：每次加1天
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-06-30'
)
SELECT 
    ds.dt AS 日期,
    COALESCE(COUNT(o.order_id), 0) AS 订单数,
    COALESCE(SUM(o.total_amount), 0) AS 销售额
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.dt AND o.status != 'cancelled'
GROUP BY ds.dt
ORDER BY ds.dt;

要点：递归 CTE 生成连续日期序列，再 LEFT JOIN 订单表，确保没有订单的日期也能显示（值为0），适合画趋势图。

❓ 常见问题

Q：CTE 和临时表有什么区别？ A： CTE 只在当前语句内有效，语句结束即消失；临时表在整个会话内有效，可以被多条 SQL 语句引用。CTE 适合单次复杂查询的拆分，临时表适合需要多次访问中间结果的场景。

Q：递归 CTE 会无限循环吗？ A：如果没有正确的终止条件，会的。确保递归部分有 WHERE 条件限制深度或范围。MySQL 有 cte_max_recursion_depth 参数（默认1000）防止无限递归。

Q：CTE 能不能被索引？ A： CTE 本身不能创建索引。如果中间结果需要索引来优化性能，应该使用临时表，临时表支持索引。

Q：多个 CTE 之间能互相引用吗？ A：可以。在同一个 WITH 子句中定义的多个 CTE，后面的可以引用前面的。但不能交叉引用（CTE A 引用 CTE B，同时 CTE B 又引用 CTE A）。

📖 小节

技术	作用域	可复用	递归	索引
子查询	单条语句	❌	❌	❌
CTE	单条语句	✅ 多次引用	✅	❌
临时表	整个会话	✅ 多条SQL	—	✅

CTE 用 WITH 定义，提升复杂查询的可读性和可维护性
递归 CTE 用 WITH RECURSIVE，适合树形数据遍历和序列生成
临时表在会话结束时自动删除，适合跨 SQL 共享中间结果

📝 作业

使用 CTE 找出每个部门工资最高的员工，并显示其工资与部门平均工资的差值。
使用递归 CTE 生成 1 到 20 的数字序列。
使用临时表存储每个客户的订单汇总数据，然后基于临时表进行客户等级分析。
思考：如果一个 CTE 被主查询引用了两次，它会被执行两次吗？用 EXPLAIN 验证。

下一课

👉 21-transactions - 事务处理：学习 SQL 事务的 ACID 特性、BEGIN/COMMIT/ROLLBACK 语法、SAVEPOINT 保存点，以及事务隔离级别和死锁概念。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

404 Not Found

CTE与临时表

CTE与临时表

🌍 生活类比

🎯 核心概念

WITH 子句 / CTE

递归 CTE

CTE vs 子查询

临时表 — CREATE TEMPORARY TABLE

CTE 适用场景总结

WITH RECURSIVE 遍历树形数据

📝 基本语法

📌 示例

示例：用 CTE 拆分复杂查询 — 部门人均销售额分析

示例：递归 CTE — 员工管理层级

🎬 场景演练

场景一：分步骤计算客户生命周期价值

场景二：生成日期序列用于销售趋势分析

❓ 常见问题

📖 小节

📝 作业

下一课

🙏 帮我们做得更好