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

今日关键词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 最实用的地方是可以一个接一个写像搭积木一样。 实战部门薪资统计 排名要把部门总薪资、平均薪资、排名全算出来拆成三级 CTEWITHdepartment_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,CASEWHENsalary10000THEN高薪WHENsalary5000THEN中薪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.level1FROMemployees eINNERJOINrecursive_cte rONe.manager_idr.id)SELECT*FROMrecursive_cte;分两部分基础查询先找到起点没有上级的节点递归部分用起点往下找一层一层查找不到新数据就停 递归的逻辑就像你查家谱先找到太爷爷起点然后一层层往下找子子孙孙。 实战部门层级查询WITHRECURSIVE department_treeAS(SELECTid,name,parent_id,1aslevelFROMdepartmentsWHEREparent_idISNULLUNIONALLSELECTd.id,d.name,d.parent_id,dt.level1FROMdepartments dINNERJOINdepartment_tree dtONd.parent_iddt.id)SELECT*FROMdepartment_tree;跑出来的结果idnameparent_idlevel1总部NULL12销售部123技术部124UI 组235前端组236后端组33以前实现这个要写存储过程或者复杂的自连接现在一行 WITH RECURSIVE 搞定。做权限树、商品分类的同学这个技能必须有四、阶段式递归的实战场景 场景一商品分类树和部门层级类似就是把部门换成商品WITHRECURSIVE product_treeAS(SELECTid,name,parent_id,1aslevelFROMproductsWHEREparent_idISNULLUNIONALLSELECTp.id,p.name,p.parent_id,pt.level1FROMproducts pINNERJOINproduct_tree ptONp.parent_idpt.id)SELECT*FROMproduct_tree; 场景二数据溯源排查数据问题时经常要用 —— 找到某个记录的来源一层一层往上找WITHRECURSIVE data_traceAS(SELECTid,data,parent_id,1astrace_levelFROMaudit_logWHEREid12345UNIONALLSELECTa.id,a.data,a.parent_id,dt.trace_level1FROMaudit_log aINNERJOINdata_trace dtONa.iddt.parent_id)SELECT*FROMdata_trace; 这个是向上追溯和前面的向下展开方向相反。核心区别在 JOIN 条件上向下查是子.parent_id 父.id向上查是父.id 子.parent_id。 场景三多阶段业务逻辑拆解客户分层这种需求拆成几步更清楚WITHstage1AS(SELECTid,name,email,created_atFROMcustomersWHEREstatusactive),stage2AS(SELECTc.id,c.name,SUM(o.amount)astotal_spentFROMstage1 cLEFTJOINorders oONc.ido.customer_idGROUPBYc.id,c.name),stage3AS(SELECTid,name,total_spent,CASEWHENtotal_spent10000THENVIPWHENtotal_spent5000THEN普通 VIPWHENtotal_spent1000THEN新客户ELSE潜在客户ENDascustomer_levelFROMstage2)SELECT*FROMstage3;第一层筛活跃客户第二层算消费总额第三层打标签。每一步干干净净改逻辑也方便。 场景四审批流程追踪WITHRECURSIVE approval_traceAS(SELECTid,process_id,user_id,status,1asstageFROMapprovalsWHEREprocess_idP12345ANDstatuspendingUNIONALLSELECTa.id,a.process_id,a.user_id,a.status,at.stage1FROMapprovals aINNERJOINapproval_trace atONa.process_idat.process_idANDa.idat.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.level1FROMdepartments dINNERJOINsafe_loop slONd.parent_idsl.idWHEREsl.level10)SELECT*FROMsafe_loop;❌ 坑二用了 UNION 而不是 UNION ALLUNION 要去重多一层开销。递归 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.0CTE 功能不可用建议升级或使用其他方式替代。