📌 今日关键词：**CTE、公共表表达式、递归查询、阶段式递归、WITH、树形结构**

大家好，我是 **数据库小学妹** 👋

前面我们学过子查询、窗口函数这些进阶技能。今天我要分享一个让我"相见恨晚"的功能 —— **CTE（公共表表达式）+ 递归**。

为什么这么说？因为我一开始遇到树形结构数据（部门层级、商品分类、组织架构）的时候，子查询套子查询，写到最后自己都绕晕了，性能还差。后来发现了CTE+递归这个组合，SQL写得清爽多了！

今天小学妹就带你从CTE基础到递归实战，一步步把这个技能掌握。

---

## 一、CTE 是什么？告别嵌套地狱

啥是CTE？你就理解成给一段查询结果起个名字，后面想用直接写名字就行。

就像 Excel 里给某个区域起名，后面公式里直接用那个名，不用每次都重写那片区域。

### 基础语法

```sql
WITH employee_cte AS (
    SELECT 
        id, 
        name, 
        manager_id, 
        salary
    FROM employees
    WHERE manager_id IS NULL
)
SELECT * FROM employee_cte;
```

WITH 后面就是 CTE 的名字，AS 括号里是查询内容。最后用这个临时名字来查。

> 💡CTE 只在这次查询里有效，查完就没了，不会污染数据库。

### CTE vs 子查询：有啥区别？

| 场景 | CTE | 子查询 |
|------|-----|--------|
| 代码可读性 | 清爽，一层一层 | 嵌套多了看瞎眼 |
| 复用性 | 一个 CTE 多地方引用 | 每次都要重写 |
| 调试 | 方便，单独查 CTE | 麻烦，拆开要重寫 |
| 性能 | 差不多 | 差不多 |

用子查询套多了自己都看不下去，CTE 就是来解决这个问题的。

---

## 二、CTE 嵌套着用：复杂查询变简单

CTE 最实用的地方是可以一个接一个写，像搭积木一样。

### 💻 实战：部门薪资统计 + 排名

要把部门总薪资、平均薪资、排名全算出来，拆成三级 CTE：

```sql
WITH department_salary AS (
    SELECT 
        department_id,
        SUM(salary) as total_salary
    FROM employees
    GROUP BY department_id
),
average_salary AS (
    SELECT 
        department_id,
        total_salary,
        total_salary / COUNT(*) as avg_salary
    FROM department_salary
),
department_rank AS (
    SELECT 
        department_id,
        avg_salary,
        RANK() OVER (ORDER BY avg_salary DESC) as rank
    FROM average_salary
)
SELECT * FROM department_rank;
```

第一层算总薪资，第二层算平均，第三层加排名。一层一层往下走，每层干一件事，逻辑清清楚楚。

> 💡CTE 之间可以互相引用。后面的 CTE 可以直接用前面 CTE 的名字，就像引用表一样。

### 配合 CASE WHEN 做数据分类

```sql
WITH employee_data AS (
    SELECT 
        id,
        name,
        salary,
        CASE 
            WHEN salary > 10000 THEN '高薪'
            WHEN salary > 5000 THEN '中薪'
            ELSE '低薪'
        END as salary_level
    FROM employees
),
high_salary_employees AS (
    SELECT *
    FROM employee_data
    WHERE salary_level = '高薪'
)
SELECT * FROM high_salary_employees;
```

第一层先分类，第二层再筛选。写起来比嵌套子查询顺多了。

---

## 三、递归 CTE：处理树形结构的神器

递归 CTE 是 CTE 的进阶用法，专门用来查层级数据 —— 组织架构、商品分类、审批流程这些场景太常用了！
### 语法结构

```sql
WITH RECURSIVE recursive_cte AS (
    -- 基础查询（起点）
    SELECT 
        id,
        name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归部分（自己调用自己）
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        r.level + 1
    FROM employees e
    INNER JOIN recursive_cte r ON e.manager_id = r.id
)
SELECT * FROM recursive_cte;
```

分两部分：
1. **基础查询**：先找到"起点"（没有上级的节点）
2. **递归部分**：用起点往下找，一层一层查，找不到新数据就停

> 💡 递归的逻辑就像你查家谱：先找到太爷爷（起点），然后一层层往下找子子孙孙。

### 💻 实战：部门层级查询

```sql
WITH RECURSIVE department_tree AS (
    SELECT 
        id,
        name,
        parent_id,
        1 as level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        d.id,
        d.name,
        d.parent_id,
        dt.level + 1
    FROM departments d
    INNER JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT * FROM department_tree;
```

跑出来的结果：

| id | name | parent_id | level |
|----|------|-----------|-------|
| 1 | 总部 | NULL | 1 |
| 2 | 销售部 | 1 | 2 |
| 3 | 技术部 | 1 | 2 |
| 4 | UI 组 | 2 | 3 |
| 5 | 前端组 | 2 | 3 |
| 6 | 后端组 | 3 | 3 |

以前实现这个要写存储过程或者复杂的自连接，现在一行 WITH RECURSIVE 搞定。做权限树、商品分类的同学，这个技能必须有！

---

## 四、阶段式递归的实战场景

### 📚 场景一：商品分类树

和部门层级类似，就是把部门换成商品：

```sql
WITH RECURSIVE product_tree AS (
    SELECT 
        id,
        name,
        parent_id,
        1 as level
    FROM products
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        p.id,
        p.name,
        p.parent_id,
        pt.level + 1
    FROM products p
    INNER JOIN product_tree pt ON p.parent_id = pt.id
)
SELECT * FROM product_tree;
```

### 📚 场景二：数据溯源

排查数据问题时经常要用 —— 找到某个记录的来源，一层一层往上找：

```sql
WITH RECURSIVE data_trace AS (
    SELECT 
        id,
        data,
        parent_id,
        1 as trace_level
    FROM audit_log
    WHERE id = 12345
    
    UNION ALL
    
    SELECT 
        a.id,
        a.data,
        a.parent_id,
        dt.trace_level + 1
    FROM audit_log a
    INNER JOIN data_trace dt ON a.id = dt.parent_id
)
SELECT * FROM data_trace;
```

> 💡 这个是"向上追溯"，和前面的"向下展开"方向相反。核心区别在 JOIN 条件上：向下查是 `子.parent_id = 父.id`，向上查是 `父.id = 子.parent_id`。

### 📚 场景三：多阶段业务逻辑拆解

客户分层这种需求，拆成几步更清楚：

```sql
WITH stage1 AS (
    SELECT id, name, email, created_at
    FROM customers
    WHERE status = 'active'
),
stage2 AS (
    SELECT 
        c.id,
        c.name,
        SUM(o.amount) as total_spent
    FROM stage1 c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
),
stage3 AS (
    SELECT 
        id,
        name,
        total_spent,
        CASE 
            WHEN total_spent > 10000 THEN 'VIP'
            WHEN total_spent > 5000 THEN '普通 VIP'
            WHEN total_spent > 1000 THEN '新客户'
            ELSE '潜在客户'
        END as customer_level
    FROM stage2
)
SELECT * FROM stage3;
```

第一层筛活跃客户，第二层算消费总额，第三层打标签。每一步干干净净，改逻辑也方便。

### 📚 场景四：审批流程追踪

```sql
WITH RECURSIVE approval_trace AS (
    SELECT 
        id,
        process_id,
        user_id,
        status,
        1 as stage
    FROM approvals
    WHERE process_id = 'P12345'
      AND status = 'pending'
    
    UNION ALL
    
    SELECT 
        a.id,
        a.process_id,
        a.user_id,
        a.status,
        at.stage + 1
    FROM approvals a
    INNER JOIN approval_trace at ON 
        a.process_id = at.process_id 
        AND a.id = at.next_approval_id
)
SELECT * FROM approval_trace;
```

这个在公司内部系统里很常用，查一条审批流到了哪一步、还有谁需要审批。

---

## 五、CTE + 窗口函数：强强联合

CTE 和窗口函数不冲突，经常混着用。CTE 负责拆分逻辑，窗口函数负责排名聚合。

比如同时做部门统计和员工排名：

```sql
WITH employee_cte AS (
    SELECT 
        id,
        name,
        department_id,
        salary,
        COUNT(*) OVER (PARTITION BY department_id) as dept_count,
        SUM(salary) OVER (PARTITION BY department_id) as dept_total
    FROM employees
),
ranked_employees AS (
    SELECT 
        id,
        name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
    FROM employee_cte
)
SELECT * FROM ranked_employees;
```



---

## 六、新手避坑指南

### ❌ 坑一：忘记加递归限制

不加限制的话，万一数据有环，查起来就停不了了：

```sql
-- 错误示例：无限递归
WITH RECURSIVE infinite_loop AS (
    SELECT id, name FROM departments
    UNION ALL
    SELECT id, name FROM infinite_loop
)
SELECT * FROM infinite_loop;
```

```sql
-- ✅ 正确写法：加递归限制
WITH RECURSIVE safe_loop AS (
    SELECT 
        id, name, 1 as level
    FROM departments
    UNION ALL
    SELECT 
        id, name, sl.level + 1
    FROM departments d
    INNER JOIN safe_loop sl ON d.parent_id = sl.id
    WHERE sl.level < 10
)
SELECT * FROM safe_loop;
```

### ❌ 坑二：用了 UNION 而不是 UNION ALL

UNION 要去重，多一层开销。递归 CTE 里基本都用 UNION ALL。

### ❌ 坑三：递归字段没建索引

递归字段（比如 parent_id、manager_id）一定要建索引，不然递归查询会慢到怀疑人生。

```sql
CREATE INDEX idx_parent_id ON departments(parent_id);
CREATE INDEX idx_manager_id ON employees(manager_id);
```

### ❌ 坑四：MySQL 版本不支持

CTE 是 MySQL 8.0 才有的功能！如果你还在用 5.7，升级或者用其他方式替代。

---

## 七、今日学习心得

1. **CTE 让复杂查询变清爽**，一层一层写，比嵌套子查询好维护多了
2. **递归 CTE 是树形数据的好工具**，组织架构、商品分类、审批流程都能用
3. **阶段式拆解是写 SQL 的好习惯**，复杂业务拆成几步，每步干净利落
4. **注意加递归限制和建索引**，这两个坑我踩过，别让大家再踩了
5. **CTE + 窗口函数** 组合起来，能处理更多场景

👋 我是 **数据库小学妹**，一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕

---

*本文为个人学习总结，所有示例基于 MySQL 8.0+。如果你的版本低于 8.0，CTE 功能不可用，建议升级或使用其他方式替代。*


前面我们学过子查询、窗口函数这些进阶技能。今天我要分享一个让我"相见恨晚"的功能 —— CTE（公共表表达式）+ 递归。

CTE+阶段式递归：用公共表表达式搞定复杂业务逻辑，告别SQL难题！

数据库小学妹带你轻松掌握CTE（公共表表达式）与递归查询！从基础语法、多级嵌套到树形结构处理（如部门/商品分类），详解WITH、WITH RECURSIVE及阶段式递归实战。告别子查询嵌套地狱，提升可读性与性能，附避坑指南（索引、层级限制等）。MySQL 8.0+ 必学利器！

数据库

后端

本文详细解析CTE（公共表表达式）与递归查询在SQL中的应用，适合处理树形结构数据如部门层级、商品分类等。通过基础语法、嵌套使用及递归实战案例，展示如何替代复杂子查询，提升代码可读性与性能。重点包括递归CTE的语法结构、阶段式拆解业务逻辑，以及常见避坑指南如索引优化和递归限制，帮助开发者高效管理层级数据。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

CTE+阶段式递归：用公共表表达式搞定复杂业务逻辑，告别SQL难题！-腾讯云开发者社区-腾讯云

CTE+阶段式递归：用公共表表达式搞定复杂业务逻辑，告别SQL难题！

CTE+阶段式递归：用公共表表达式搞定复杂业务逻辑，告别SQL难题！

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐