📌 今日关键词：CTE、公共表表达式、递归查询、阶段式递归、WITH、树形结构
大家好，我是 数据库小学妹 👋
前面我们学过子查询、窗口函数这些进阶技能。今天我要分享一个让我"相见恨晚"的功能 —— CTE（公共表表达式）+ 递归。
为什么这么说？因为我一开始遇到树形结构数据（部门层级、商品分类、组织架构）的时候，子查询套子查询，写到最后自己都绕晕了，性能还差。后来发现了CTE+递归这个组合，SQL写得清爽多了！
今天小学妹就带你从CTE基础到递归实战，一步步把这个技能掌握。
一、CTE 是什么？告别嵌套地狱
啥是CTE？你就理解成给一段查询结果起个名字，后面想用直接写名字就行。
就像 Excel 里给某个区域起名，后面公式里直接用那个名，不用每次都重写那片区域。
基础语法
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：
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 做数据分类
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 的进阶用法，专门用来查层级数据 —— 组织架构、商品分类、审批流程这些场景太常用了！
语法结构
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;
分两部分：
- 基础查询：先找到"起点"（没有上级的节点）
- 递归部分：用起点往下找，一层一层查，找不到新数据就停
💡 递归的逻辑就像你查家谱：先找到太爷爷（起点），然后一层层往下找子子孙孙。
💻 实战：部门层级查询
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 搞定。做权限树、商品分类的同学，这个技能必须有！
四、阶段式递归的实战场景
📚 场景一：商品分类树
和部门层级类似，就是把部门换成商品：
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;
📚 场景二：数据溯源
排查数据问题时经常要用 —— 找到某个记录的来源，一层一层往上找：
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。
📚 场景三：多阶段业务逻辑拆解
客户分层这种需求，拆成几步更清楚：
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;
第一层筛活跃客户，第二层算消费总额，第三层打标签。每一步干干净净，改逻辑也方便。
📚 场景四：审批流程追踪
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 负责拆分逻辑，窗口函数负责排名聚合。
比如同时做部门统计和员工排名：
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;
六、新手避坑指南
❌ 坑一：忘记加递归限制
不加限制的话，万一数据有环，查起来就停不了了：
-- 错误示例：无限递归
WITH RECURSIVE infinite_loop AS (
SELECT id, name FROM departments
UNION ALL
SELECT id, name FROM infinite_loop
)
SELECT * FROM infinite_loop;
-- ✅ 正确写法：加递归限制
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）一定要建索引，不然递归查询会慢到怀疑人生。
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，升级或者用其他方式替代。
七、今日学习心得
- CTE 让复杂查询变清爽，一层一层写，比嵌套子查询好维护多了
- 递归 CTE 是树形数据的好工具，组织架构、商品分类、审批流程都能用
- 阶段式拆解是写 SQL 的好习惯，复杂业务拆成几步，每步干净利落
- 注意加递归限制和建索引，这两个坑我踩过，别让大家再踩了
- CTE + 窗口函数 组合起来，能处理更多场景
👋 我是 数据库小学妹，一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕
本文为个人学习总结，所有示例基于 MySQL 8.0+。如果你的版本低于 8.0，CTE 功能不可用，建议升级或使用其他方式替代。
