告别套娃式子查询：SQL WITH 语句（CTE）深度实战指南

SQL WITH / CTE 整理笔记学习目标理解WITH/CTE是什么知道它和子查询的关系并能用它完成“先求中间结果再继续查询”的两类典型练习。1. 核心概念WITH语句也叫CTE全称是Common Table Expression中文常译为“公用表表达式”。可以把它理解为“给一段子查询结果起名字”这样主查询里就能像使用普通表一样去引用它。它不是永久表也不是课程里机器翻译常说的width class、weight loss。这些都是误译正确说法是WITH clause或CTE。CTE只在紧跟着它的那一条主语句中有效语句执行结束后这个名字就失效了。从学习和书写角度可以把CTE理解成“先准备中间结果再写主查询”实际执行时数据库优化器可能会调整执行计划所以不要机械理解为“CTE 一定会被单独物化成真实临时表”。2. 基本语法2.1 单个 CTEWITHcte_name(column1,column2,...)AS(SELECT...)SELECT...FROMcte_name;说明cte_name是这段中间结果的名字。(column1, column2, ...)是可选的列名列表不写也可以。AS (...)里的查询负责产出中间结果。后面的主查询可以直接引用这个cte_name。2.2 多个 CTEWITHcte_1AS(SELECT...),cte_2AS(SELECT...FROMcte_1)SELECT...FROMcte_2;说明多个 CTE 用逗号分隔。后定义的 CTE 可以引用前面已经定义好的 CTE。这正是WITH很适合拆分复杂查询的原因。3. 课程里的理解方式这节课强调的不是“WITH 只能做什么”而是“什么时候它比嵌套子查询更清楚”。可以先记住一个实用理解先把某个中间结果写出来并起一个有意义的名字。如果后续还要继续基于这个结果做筛选、聚合或关联就在主查询里继续引用它。当同一段子查询要重复使用或者嵌套层次已经开始变乱时WITH往往比直接嵌套更好读。4. 案例 1找出薪资高于平均薪资的员工4.1 题目从员工表emp中筛选出薪资高于全表平均薪资的员工。4.2 样例数据DROPTABLEIFEXISTSemp;CREATETABLEemp(emp_idINT,emp_nameVARCHAR(50),salaryINT);INSERTINTOempVALUES(101,Mohan,40000);INSERTINTOempVALUES(102,James,50000);INSERTINTOempVALUES(103,Robin,60000);INSERTINTOempVALUES(104,Carol,70000);INSERTINTOempVALUES(105,Alice,80000);INSERTINTOempVALUES(106,Jimmy,90000);4.3 拆解思路先求整张表的平均薪资。再筛出salary 平均薪资的员工。4.4 子查询写法SELECT*FROMempWHEREsalary(SELECTCAST(AVG(salary)ASSIGNED)FROMemp);说明内层子查询先算出平均薪资。外层查询再拿员工工资去比较。这个需求本身很简单用子查询完全可以解决。4.5 WITH 写法WITHavg_sal(avg_salary)AS(SELECTCAST(AVG(salary)ASSIGNED)FROMemp)SELECTe.*FROMemp eJOINavg_sal avONe.salaryav.avg_salary;4.6 对比结论这个例子主要是为了熟悉WITH的语法和引用方式。因为中间结果只用了一次所以它不一定非得写成WITH。但通过这个例子可以先建立一个基本感觉CTE就像一个名字清晰的“中间结果表”。5. 案例 2找出销售额高于所有门店平均销售额的门店5.1 题目从sales表中找出总销售额高于所有门店平均销售额的门店。5.2 样例数据DROPTABLEIFEXISTSsales;CREATETABLEsales(store_idINT,store_nameVARCHAR(50),productVARCHAR(50),quantityINT,costINT);INSERTINTOsalesVALUES(1,Apple Originals 1,iPhone 12 Pro,1,1000),(1,Apple Originals 1,MacBook pro 13,3,2000),(1,Apple Originals 1,AirPods Pro,2,280),(2,Apple Originals 2,iPhone 12 Pro,2,1000),(3,Apple Originals 3,iPhone 12 Pro,1,1000),(3,Apple Originals 3,MacBook pro 13,1,2000),(3,Apple Originals 3,MacBook Air,4,1100),(3,Apple Originals 3,iPhone 12,2,1000),(3,Apple Originals 3,AirPods Pro,3,280),(4,Apple Originals 4,iPhone 12 Pro,2,1000),(4,Apple Originals 4,MacBook pro 13,1,2500);说明本例完全沿用课程脚本直接使用SUM(cost)计算门店销售额。在真实业务里如果cost表示单价通常还要结合quantity计算比如SUM(quantity * cost)。5.3 拆解思路这题比案例 1 更适合使用WITH因为它天然可以拆成三步先求每个门店的总销售额。再求“所有门店总销售额”的平均值。最后把“每个门店的总销售额”与“所有门店平均销售额”进行比较。5.4 先求每个门店的总销售额SELECTstore_id,SUM(cost)AStotal_sales_per_storeFROMsalesGROUPBYstore_id;根据课程样例数据结果分别是1 号门店32802 号门店10003 号门店53804 号门店35005.5 再求所有门店平均销售额SELECTCAST(AVG(total_sales_per_store)ASSIGNED)ASavg_sale_for_all_storeFROM(SELECTstore_id,SUM(cost)AStotal_sales_per_storeFROMsalesGROUPBYstore_id)x;这里的平均值是(3280 1000 5380 3500) / 4 32905.6 纯子查询写法SELECT*FROM(SELECTstore_id,SUM(cost)AStotal_sales_per_storeFROMsalesGROUPBYstore_id)total_salesJOIN(SELECTCAST(AVG(total_sales_per_store)ASSIGNED)ASavg_sale_for_all_storeFROM(SELECTstore_id,SUM(cost)AStotal_sales_per_storeFROMsalesGROUPBYstore_id)x)avg_salesONtotal_sales.total_sales_per_storeavg_sales.avg_sale_for_all_store;问题在于SUM(cost) ... GROUP BY store_id这段逻辑被写了两次。嵌套层级变深后可读性会明显下降。后续如果逻辑再复杂一点维护和排错都会更麻烦。5.7 WITH 写法WITHtotal_salesAS(SELECTstore_id,SUM(cost)AStotal_sales_per_storeFROMsalesGROUPBYstore_id),avg_salesAS(SELECTCAST(AVG(total_sales_per_store)ASSIGNED)ASavg_sale_for_all_storeFROMtotal_sales)SELECT*FROMtotal_sales tsJOINavg_sales avONts.total_sales_per_storeav.avg_sale_for_all_store;5.8 对比结论这才是本课最典型的WITH使用场景。total_sales先把“每店总销售额”这个中间结果命名出来。avg_sales再直接基于total_sales做下一步计算不必重复写同一段子查询。主查询只需要表达“把两个中间结果关联起来并做比较”意图会清晰很多。最终结果应当是3 号门店4 号门店6. MySQL 易错点CAST(... AS int)不兼容老师演示时使用了把平均值转成整数的思路这个思路本身没问题但如果你在 MySQL 中直接写CAST(AVG(salary)ASint)就可能报语法错误因为 MySQL 的CAST()不支持把目标类型直接写成int。在 MySQL 中更稳妥的写法是CAST(AVG(salary)ASSIGNED)或者CAST(AVG(salary)ASUNSIGNED)常见可用类型包括SIGNEDUNSIGNEDDECIMAL(M,D)CHARDATEDATETIMETIME如果你的目的只是“去掉或控制小数部分”也可以考虑ROUND()FLOOR()TRUNCATE()7. 什么时候适合用 WITH7.1 适合使用的场景同一个子查询结果要被复用多次。查询很长、很绕嵌套子查询已经开始影响阅读。你想把大问题拆成几个小步骤每一步都有明确名字。你希望先筛出一批中间结果后续再继续聚合、关联或过滤。7.2 不一定非要使用的场景只是一个非常短、非常直观、只用一次的子查询。用普通子查询写出来已经很清楚没有重复逻辑。7.3 关于性能的更稳妥理解课程里强调了WITH有助于避免重复书写和重复计算这个方向是对的。但在真实数据库里性能是否更好还要看数据库版本、优化器行为和具体 SQL 写法。所以更稳妥的结论是WITH常常能提升可读性与维护性在部分复用或先过滤后复用的场景里也可能带来更好的执行效果但不能简单理解为“只要用了 CTE 就一定更快”。8. CTE 列名列表什么时候有用课程里提到WITH avg_sal (avg_salary) AS (...)里的(avg_salary)可以不写因为很多时候 SQL 能自动继承子查询里的列名。但下面这些场景显式写列名会更清楚你想让中间结果的输出列一眼就能看懂。子查询里有聚合函数、表达式或复杂计算列。你在多表关联中遇到了重复列名。你写的是递归 CTE需要把输出列先定义清楚。示例WITHdept_avg(dept_id,avg_salary)AS(SELECTdept_id,AVG(salary)FROMempGROUPBYdept_id)SELECT*FROMdept_avg;9. 延伸补充递归 CTE这节课的重点不是递归查询但要知道普通CTE主要用于拆分复杂查询。WITH RECURSIVE还能处理树形结构、层级结构和路径展开等问题。例如WITHRECURSIVE subordinatesAS(SELECTemp_id,emp_name,manager_idFROMempWHEREemp_nameCEOUNIONALLSELECTe.emp_id,e.emp_name,e.manager_idFROMemp eJOINsubordinates sONe.manager_ids.emp_id)SELECT*FROMsubordinates;这部分先知道用途即可不是本节整理笔记的重点。10. 本节笔记结论WITH就是给中间结果命名让 SQL 能分步骤表达。它最核心的价值是复用、拆解复杂逻辑、提高可读性。简单查询不一定非要用WITH但复杂查询往往会因为WITH变得更清楚。在 MySQL 中要特别注意CAST(... AS int)的兼容性问题优先使用SIGNED或UNSIGNED。这节课最值得反复练习的是第二个门店销售额案例因为它最能体现CTE的真实价值。