📌 今日关键词：CTE、公共表表达式、递归查询、阶段式递归、WITH、树形结构
大家好，我是数据库小学妹👋
前面我们学过子查询、窗口函数这些进阶技能。今天我要分享一个让我"相见恨晚"的功能 ——CTE（公共表表达式）+ 递归。
为什么这么说？因为我一开始遇到树形结构数据（部门层级、商品分类、组织架构）的时候，子查询套子查询，写到最后自己都绕晕了，性能还差。后来发现了CTE+递归这个组合，SQL写得清爽多了！
今天小学妹就带你从CTE基础到递归实战，一步步把这个技能掌握。
一、CTE 是什么？告别嵌套地狱
啥是CTE？你就理解成给一段查询结果起个名字，后面想用直接写名字就行。
就像 Excel 里给某个区域起名，后面公式里直接用那个名，不用每次都重写那片区域。
基础语法
WITHemployee_cteAS(SELECTid,name,manager_id,salaryFROMemployeesWHEREmanager_idISNULL)SELECT*FROMemployee_cte;WITH 后面就是 CTE 的名字，AS 括号里是查询内容。最后用这个临时名字来查。
💡CTE 只在这次查询里有效，查完就没了，不会污染数据库。
CTE vs 子查询：有啥区别？
| 场景 | CTE | 子查询 |
|---|---|---|
| 代码可读性 | 清爽，一层一层 | 嵌套多了看瞎眼 |
| 复用性 | 一个 CTE 多地方引用 | 每次都要重写 |
| 调试 | 方便，单独查 CTE | 麻烦，拆开要重寫 |
| 性能 | 差不多 | 差不多 |
用子查询套多了自己都看不下去，CTE 就是来解决这个问题的。
二、CTE 嵌套着用：复杂查询变简单
CTE 最实用的地方是可以一个接一个写，像搭积木一样。
💻 实战：部门薪资统计 + 排名
要把部门总薪资、平均薪资、排名全算出来，拆成三级 CTE：
WITHdepartment_salaryAS(SELECTdepartment_id,SUM(salary)astotal_salaryFROMemployeesGROUPBYdepartment_id),average_salaryAS(SELECTdepartment_id,total_salary,total_salary/COUNT(*)asavg_salaryFROMdepartment_salary),department_rankAS(SELECTdepartment_id,avg_salary,RANK()OVER(ORDERBYavg_salaryDESC)asrankFROMaverage_salary)SELECT*FROMdepartment_rank;第一层算总薪资，第二层算平均，第三层加排名。一层一层往下走，每层干一件事，逻辑清清楚楚。
💡CTE 之间可以互相引用。后面的 CTE 可以直接用前面 CTE 的名字，就像引用表一样。
配合 CASE WHEN 做数据分类
WITHemployee_dataAS(SELECTid,name,salary,CASEWHENsalary>10000THEN'高薪'WHENsalary>5000THEN'中薪'ELSE'低薪'ENDassalary_levelFROMemployees),high_salary_employeesAS(SELECT*FROMemployee_dataWHEREsalary_level='高薪')SELECT*FROMhigh_salary_employees;第一层先分类，第二层再筛选。写起来比嵌套子查询顺多了。
三、递归 CTE：处理树形结构的神器
递归 CTE 是 CTE 的进阶用法，专门用来查层级数据 —— 组织架构、商品分类、审批流程这些场景太常用了！
语法结构
WITHRECURSIVE recursive_cteAS(-- 基础查询（起点）SELECTid,name,manager_id,1aslevelFROMemployeesWHEREmanager_idISNULLUNIONALL-- 递归部分（自己调用自己）SELECTe.id,e.name,e.manager_id,r.level+1FROMemployees eINNERJOINrecursive_cte rONe.manager_id=r.id)SELECT*FROMrecursive_cte;分两部分：
- 基础查询：先找到"起点"（没有上级的节点）
- 递归部分：用起点往下找，一层一层查，找不到新数据就停
💡 递归的逻辑就像你查家谱：先找到太爷爷（起点），然后一层层往下找子子孙孙。
💻 实战：部门层级查询
WITHRECURSIVE department_treeAS(SELECTid,name,parent_id,1aslevelFROMdepartmentsWHEREparent_idISNULLUNIONALLSELECTd.id,d.name,d.parent_id,dt.level+1FROMdepartments dINNERJOINdepartment_tree dtONd.parent_id=dt.id)SELECT*FROMdepartment_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 搞定。做权限树、商品分类的同学，这个技能必须有！
四、阶段式递归的实战场景
📚 场景一：商品分类树
和部门层级类似，就是把部门换成商品：
WITHRECURSIVE product_treeAS(SELECTid,name,parent_id,1aslevelFROMproductsWHEREparent_idISNULLUNIONALLSELECTp.id,p.name,p.parent_id,pt.level+1FROMproducts pINNERJOINproduct_tree ptONp.parent_id=pt.id)SELECT*FROMproduct_tree;📚 场景二：数据溯源
排查数据问题时经常要用 —— 找到某个记录的来源，一层一层往上找：
WITHRECURSIVE data_traceAS(SELECTid,data,parent_id,1astrace_levelFROMaudit_logWHEREid=12345UNIONALLSELECTa.id,a.data,a.parent_id,dt.trace_level+1FROMaudit_log aINNERJOINdata_trace dtONa.id=dt.parent_id)SELECT*FROMdata_trace;💡 这个是"向上追溯"，和前面的"向下展开"方向相反。核心区别在 JOIN 条件上：向下查是
子.parent_id = 父.id，向上查是父.id = 子.parent_id。
📚 场景三：多阶段业务逻辑拆解
客户分层这种需求，拆成几步更清楚：
WITHstage1AS(SELECTid,name,email,created_atFROMcustomersWHEREstatus='active'),stage2AS(SELECTc.id,c.name,SUM(o.amount)astotal_spentFROMstage1 cLEFTJOINorders oONc.id=o.customer_idGROUPBYc.id,c.name),stage3AS(SELECTid,name,total_spent,CASEWHENtotal_spent>10000THEN'VIP'WHENtotal_spent>5000THEN'普通 VIP'WHENtotal_spent>1000THEN'新客户'ELSE'潜在客户'ENDascustomer_levelFROMstage2)SELECT*FROMstage3;第一层筛活跃客户，第二层算消费总额，第三层打标签。每一步干干净净，改逻辑也方便。
📚 场景四：审批流程追踪
WITHRECURSIVE approval_traceAS(SELECTid,process_id,user_id,status,1asstageFROMapprovalsWHEREprocess_id='P12345'ANDstatus='pending'UNIONALLSELECTa.id,a.process_id,a.user_id,a.status,at.stage+1FROMapprovals aINNERJOINapproval_trace atONa.process_id=at.process_idANDa.id=at.next_approval_id)SELECT*FROMapproval_trace;这个在公司内部系统里很常用，查一条审批流到了哪一步、还有谁需要审批。
五、CTE + 窗口函数：强强联合
CTE 和窗口函数不冲突，经常混着用。CTE 负责拆分逻辑，窗口函数负责排名聚合。
比如同时做部门统计和员工排名：
WITHemployee_cteAS(SELECTid,name,department_id,salary,COUNT(*)OVER(PARTITIONBYdepartment_id)asdept_count,SUM(salary)OVER(PARTITIONBYdepartment_id)asdept_totalFROMemployees),ranked_employeesAS(SELECTid,name,department_id,salary,ROW_NUMBER()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)asrankFROMemployee_cte)SELECT*FROMranked_employees;六、新手避坑指南
❌ 坑一：忘记加递归限制
不加限制的话，万一数据有环，查起来就停不了了：
-- 错误示例：无限递归WITHRECURSIVE infinite_loopAS(SELECTid,nameFROMdepartmentsUNIONALLSELECTid,nameFROMinfinite_loop)SELECT*FROMinfinite_loop;-- ✅ 正确写法：加递归限制WITHRECURSIVE safe_loopAS(SELECTid,name,1aslevelFROMdepartmentsUNIONALLSELECTid,name,sl.level+1FROMdepartments dINNERJOINsafe_loop slONd.parent_id=sl.idWHEREsl.level<10)SELECT*FROMsafe_loop;❌ 坑二：用了 UNION 而不是 UNION ALL
UNION 要去重，多一层开销。递归 CTE 里基本都用 UNION ALL。
❌ 坑三：递归字段没建索引
递归字段（比如 parent_id、manager_id）一定要建索引，不然递归查询会慢到怀疑人生。
CREATEINDEXidx_parent_idONdepartments(parent_id);CREATEINDEXidx_manager_idONemployees(manager_id);❌ 坑四：MySQL 版本不支持
CTE 是 MySQL 8.0 才有的功能！如果你还在用 5.7，升级或者用其他方式替代。
七、今日学习心得
- CTE 让复杂查询变清爽，一层一层写，比嵌套子查询好维护多了
- 递归 CTE 是树形数据的好工具，组织架构、商品分类、审批流程都能用
- 阶段式拆解是写 SQL 的好习惯，复杂业务拆成几步，每步干净利落
- 注意加递归限制和建索引，这两个坑我踩过，别让大家再踩了
- CTE + 窗口函数组合起来，能处理更多场景
👋 我是数据库小学妹，一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕
本文为个人学习总结，所有示例基于 MySQL 8.0+。如果你的版本低于 8.0，CTE 功能不可用，建议升级或使用其他方式替代。
