【MySQL全面教学】MySQL子查询与高级查询Day7（2026年）
写在前面
大家好，欢迎来到MySQL全面教学系列的第7天！昨天我们学习了多表查询与JOIN，掌握了表与表之间关联查询的技能。今天，我们将进入更高级的查询领域——子查询与高级查询。
子查询是嵌套在其他查询中的查询，它可以解决很多复杂的业务问题。而窗口函数和CTE（公用表表达式）则是MySQL 8.0带来的强大特性，让复杂的数据分析变得简单优雅。
这是单表查询系列的最后一篇，内容较多但非常实用，让我们开始吧！
目录
- 写在前面
- 一、子查询分类
- 二、WHERE中的子查询
- 2.1 IN子查询
- 2.2 EXISTS子查询
- 2.3 比较运算符子查询
- 2.4 ALL和ANY/SOME
- 三、FROM中的子查询（派生表）
- 3.1 派生表优化
- 四、SELECT中的子查询（关联子查询）
- 五、UNION和UNION ALL
- 5.1 UNION vs UNION ALL
- 5.2 UNION注意事项
- 六、窗口函数（MySQL 8.0+）
- 6.1 窗口函数语法
- 6.2 排名函数
- 6.3 分组排名
- 6.4 偏移函数（LEAD/LAG）
- 6.5 聚合窗口函数
- 七、CTE公用表表达式（WITH子句）
- 7.1 基本CTE
- 7.2 多个CTE
- 7.3 递归CTE
- 八、实战：复杂业务查询场景
- 8.1 连续登录天数统计
- 8.2 分组TOP N
- 8.3 同期群分析（Cohort Analysis）
- 九、踩坑提醒与经验之谈
- 9.1 子查询性能问题
- 9.2 IN子查询的NULL陷阱
- 9.3 派生表没有索引
- 9.4 窗口函数与GROUP BY混用
- 十、面试高频考点
- 10.1 IN和EXISTS的区别？
- 10.2 窗口函数和GROUP BY的区别？
- 10.3 如何优化子查询？
- 10.4 CTE和派生表的区别？
- 10.5 如何实现分组TOP N？
- 十一、总结
- 下一步预告
- 参考资料
- 互动话题
一、子查询分类
子查询（Subquery）是嵌套在另一个SQL语句中的SELECT语句。根据返回结果的不同，子查询可以分为以下几类：
| 子查询类型 | 返回值 | 使用场景 | 示例 |
|---|---|---|---|
| 标量子查询 | 单行单列 | 比较运算 | SELECT * FROM t WHERE col = (SELECT …) |
| 行子查询 | 单行多列 | 行比较 | SELECT * FROM t WHERE (a,b) = (SELECT …) |
| 表子查询 | 多行多列 | FROM子句 | SELECT * FROM (SELECT …) AS t |
二、WHERE中的子查询
2.1 IN子查询
IN用于判断某值是否在子查询返回的结果集中。
-- 查询有订单的用户信息SELECT*FROMusersWHEREuser_idIN(SELECTDISTINCTuser_idFROMorders);-- 查询购买了特定类别商品的用户SELECT*FROMusersWHEREuser_idIN(SELECTDISTINCTo.user_idFROMorders oJOINorder_items oiONo.order_id=oi.order_idJOINproducts pONoi.product_id=p.product_idWHEREp.category_id=1);2.2 EXISTS子查询
EXISTS用于判断子查询是否返回结果，返回布尔值。
-- 查询有订单的用户（与IN效果相同）SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_id=u.user_id);-- 查询没有订单的用户SELECT*FROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_id=u.user_id);IN vs EXISTS对比：
| 特性 | IN | EXISTS |
|---|---|---|
| 执行方式 | 先执行子查询 | 相关子查询，逐行判断 |
| 性能 | 子查询结果小时快 | 子查询结果大时快 |
| NULL处理 | 需注意NULL值 | 不受NULL影响 |
| 使用场景 | 固定值列表 | 关联条件复杂时 |
2.3 比较运算符子查询
-- 查询订单金额大于平均订单金额的记录SELECT*FROMordersWHEREtotal_amount>(SELECTAVG(total_amount)FROMorders);-- 查询每个用户超过其平均订单金额的订单SELECTo1.*FROMorders o1WHEREo1.total_amount>(SELECTAVG(o2.total_amount)FROMorders o2WHEREo2.user_id=o1.user_id);2.4 ALL和ANY/SOME
-- 查询订单金额大于所有用户平均消费的用户（ALL）SELECT*FROMusers uWHEREtotal_spent>ALL(SELECTAVG(total_amount)FROMordersGROUPBYuser_id);-- 查询订单金额大于任意一个用户平均消费的用户（ANY）SELECT*FROMusers uWHEREtotal_spent>ANY(SELECTAVG(total_amount)FROMordersGROUPBYuser_id);三、FROM中的子查询（派生表）
子查询在FROM子句中作为临时表使用，必须指定别名。
-- 查询每个用户的订单统计SELECTu.username,t.order_count,t.total_amount,t.avg_amountFROMusers uJOIN(SELECTuser_id,COUNT(*)ASorder_count,SUM(total_amount)AStotal_amount,AVG(total_amount)ASavg_amountFROMordersGROUPBYuser_id)tONu.user_id=t.user_id;3.1 派生表优化
MySQL 5.7+会对派生表进行合并优化：
-- MySQL会自动将派生表合并到外层查询SELECT*FROM(SELECT*FROMordersWHEREstatus='completed')ASoWHEREo.amount>100;-- 等同于SELECT*FROMordersWHEREstatus='completed'ANDamount>100;四、SELECT中的子查询（关联子查询）
SELECT中的子查询会为每一行执行一次，称为关联子查询。
-- 查询用户及其订单数量SELECTu.user_id,u.username,(SELECTCOUNT(*)FROMorders oWHEREo.user_id=u.user_id)ASorder_countFROMusers u;-- 查询每个订单的用户消费排名SELECTo.order_id,o.user_id,o.total_amount,(SELECTCOUNT(*)+1FROMorders o2WHEREo2.user_id=o.user_idANDo2.total_amount>o.total_amount)ASrank_in_userFROMorders o;踩坑提醒：SELECT中的关联子查询性能较差，每行都要执行一次子查询。大数据量时建议使用JOIN替代。
五、UNION和UNION ALL
UNION用于合并多个SELECT语句的结果集。
5.1 UNION vs UNION ALL
| 特性 | UNION | UNION ALL |
|---|---|---|
| 去重 | 自动去重 | 不去重 |
| 性能 | 较慢（需要比较） | 较快 |
| 使用场景 | 需要唯一结果 | 允许重复结果 |
-- UNION去重SELECTcityFROMusersWHEREage>30UNIONSELECTcityFROMusersWHEREtotal_spent>1000;-- UNION ALL保留所有记录SELECT'VIP'ASuser_type,user_id,usernameFROMusersWHEREtotal_spent>10000UNIONALLSELECT'普通',user_id,usernameFROMusersWHEREtotal_spent<=10000;5.2 UNION注意事项
-- 列数必须相同-- 列名以第一个SELECT为准-- 对应列的数据类型要兼容SELECTuser_id,usernameFROMusersUNIONSELECTorder_id,CAST(total_amountASCHAR)FROMorders;-- 类型转换六、窗口函数（MySQL 8.0+）
窗口函数是MySQL 8.0引入的强大特性，用于在结果集的"窗口"上进行计算。
6.1 窗口函数语法
function_name(expression)OVER([PARTITIONBYpartition_expression][ORDERBYorder_expression][frame_clause])6.2 排名函数
| 函数 | 说明 | 相同值处理 |
|---|---|---|
| ROW_NUMBER() | 连续排名 | 相同值不同排名 |
| RANK() | 跳跃排名 | 相同值同排名，跳过后续 |
| DENSE_RANK() | 密集排名 | 相同值同排名，不跳过 |
-- 查询用户消费排名SELECTuser_id,username,total_spent,ROW_NUMBER()OVER(ORDERBYtotal_spentDESC)ASrow_num,RANK()OVER(ORDERBYtotal_spentDESC)ASrank_num,DENSE_RANK()OVER(ORDERBYtotal_spentDESC)ASdense_rank_numFROMusers;结果示例：
| user_id | username | total_spent | row_num | rank_num | dense_rank_num |
|---|---|---|---|---|---|
| 1 | 张三 | 10000 | 1 | 1 | 1 |
| 2 | 李四 | 8000 | 2 | 2 | 2 |
| 3 | 王五 | 8000 | 3 | 2 | 2 |
| 4 | 赵六 | 5000 | 4 | 4 | 3 |
6.3 分组排名
-- 按城市分组，查询每个城市的用户消费排名SELECTcity,username,total_spent,RANK()OVER(PARTITIONBYcityORDERBYtotal_spentDESC)AScity_rankFROMusers;6.4 偏移函数（LEAD/LAG）
-- 查询每月销售额及环比变化SELECTmonth,sales,LAG(sales)OVER(ORDERBYmonth)ASprev_month_sales,sales-LAG(sales)OVER(ORDERBYmonth)ASmonth_over_month,LEAD(sales)OVER(ORDERBYmonth)ASnext_month_salesFROMmonthly_sales;6.5 聚合窗口函数
-- 累计求和SELECTorder_date,daily_sales,SUM(daily_sales)OVER(ORDERBYorder_date)AScumulative_salesFROMdaily_summary;-- 移动平均SELECTdate,value,AVG(value)OVER(ORDERBYdateROWSBETWEEN6PRECEDINGANDCURRENTROW)AS7_day_avgFROMdaily_data;七、CTE公用表表达式（WITH子句）
CTE（Common Table Expression）使用WITH子句定义临时结果集，可以多次引用。
7.1 基本CTE
-- 定义CTEWITHuser_statsAS(SELECTuser_id,COUNT(*)ASorder_count,SUM(total_amount)AStotal_spentFROMordersGROUPBYuser_id)-- 使用CTESELECTu.username,us.order_count,us.total_spentFROMusers uJOINuser_stats usONu.user_id=us.user_id;7.2 多个CTE
WITHorder_statsAS(SELECTuser_id,COUNT(*)ASorder_countFROMordersGROUPBYuser_id),payment_statsAS(SELECTuser_id,SUM(amount)AStotal_paidFROMpaymentsGROUPBYuser_id)SELECTu.username,COALESCE(os.order_count,0)ASorder_count,COALESCE(ps.total_paid,0)AStotal_paidFROMusers uLEFTJOINorder_stats osONu.user_id=os.user_idLEFTJOINpayment_stats psONu.user_id=ps.user_id;7.3 递归CTE
递归CTE用于处理层级数据：
-- 查询分类的所有子分类WITHRECURSIVE category_treeAS(-- 锚成员：起始点SELECTcategory_id,category_name,parent_id,0ASlevelFROMcategoriesWHEREcategory_id=1-- 从根分类开始UNIONALL-- 递归成员SELECTc.category_id,c.category_name,c.parent_id,ct.level+1FROMcategories cJOINcategory_tree ctONc.parent_id=ct.category_id)SELECT*FROMcategory_tree;八、实战：复杂业务查询场景
8.1 连续登录天数统计
-- 统计用户最大连续登录天数WITHlogin_with_grpAS(SELECTuser_id,login_date,DATE_SUB(login_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)DAY)ASgrpFROMuser_logins)SELECTuser_id,COUNT(*)ASconsecutive_daysFROMlogin_with_grpGROUPBYuser_id,grpORDERBYconsecutive_daysDESC;8.2 分组TOP N
-- 查询每个类别销量TOP3的商品WITHranked_productsAS(SELECTcategory_id,product_id,product_name,sales_volume,RANK()OVER(PARTITIONBYcategory_idORDERBYsales_volumeDESC)ASrnkFROMproducts)SELECT*FROMranked_productsWHERErnk<=3;8.3 同期群分析（Cohort Analysis）
-- 用户留存分析WITHuser_cohortsAS(SELECTuser_id,DATE_FORMAT(MIN(order_date),'%Y-%m')AScohort_monthFROMordersGROUPBYuser_id),user_activityAS(SELECTDISTINCTu.user_id,uc.cohort_month,DATE_FORMAT(o.order_date,'%Y-%m')ASactivity_month,PERIOD_DIFF(DATE_FORMAT(o.order_date,'%Y%m'),DATE_FORMAT(uc.cohort_month,'%Y%m'))ASperiod_numFROMorders oJOINuser_cohorts ucONo.user_id=uc.user_id)SELECTcohort_month,period_num,COUNT(DISTINCTuser_id)ASuser_count,ROUND(COUNT(DISTINCTuser_id)*100.0/FIRST_VALUE(COUNT(DISTINCTuser_id))OVER(PARTITIONBYcohort_monthORDERBYperiod_num),2)ASretention_rateFROMuser_activityGROUPBYcohort_month,period_numORDERBYcohort_month,period_num;九、踩坑提醒与经验之谈
9.1 子查询性能问题
问题：关联子查询性能差
-- 低效写法：每行执行一次子查询SELECTu.*,(SELECTCOUNT(*)FROMorders oWHEREo.user_id=u.user_id)AScntFROMusers u;-- 高效写法：使用JOINSELECTu.*,COALESCE(o.cnt,0)AScntFROMusers uLEFTJOIN(SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id)oONu.user_id=o.user_id;经验之谈：能用JOIN就不用子查询，特别是SELECT中的关联子查询。
9.2 IN子查询的NULL陷阱
-- 危险！子查询返回NULL会导致结果为空SELECT*FROMusersWHEREuser_idIN(SELECTmanager_idFROMemployees);-- 如果manager_id有NULL-- 安全写法：排除NULLSELECT*FROMusersWHEREuser_idIN(SELECTmanager_idFROMemployeesWHEREmanager_idISNOTNULL);-- 或者使用EXISTSSELECT*FROMusers uWHEREEXISTS(SELECT1FROMemployees eWHEREe.manager_id=u.user_id);9.3 派生表没有索引
-- 派生表不能直接使用索引-- 如果需要优化，考虑创建临时表并加索引CREATETEMPORARYTABLEtmp_user_statsASSELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id;ALTERTABLEtmp_user_statsADDINDEXidx_user_id(user_id);-- 然后使用临时表进行JOIN9.4 窗口函数与GROUP BY混用
-- 错误：窗口函数在GROUP BY之后执行SELECTcategory_id,SUM(sales),RANK()OVER(ORDERBYSUM(sales))-- 错误！FROMproductsGROUPBYcategory_id;-- 正确：使用派生表或CTESELECTcategory_id,total_sales,RANK()OVER(ORDERBYtotal_sales)FROM(SELECTcategory_id,SUM(sales)AStotal_salesFROMproductsGROUPBYcategory_id)t;十、面试高频考点
10.1 IN和EXISTS的区别？
答案：
- IN：先执行子查询，将结果缓存，然后外层查询匹配。适合子查询结果小的情况。
- EXISTS：相关子查询，对外层每行执行子查询，找到匹配即返回。适合子查询结果大的情况。
- IN遇到NULL会有问题，EXISTS不会。
10.2 窗口函数和GROUP BY的区别？
答案：
- GROUP BY：将多行聚合成一行，行数减少
- 窗口函数：保持原有行数，为每行添加计算列
- GROUP BY后使用聚合函数，窗口函数使用OVER子句
10.3 如何优化子查询？
答案：
- 将SELECT中的关联子查询改为JOIN
- 将IN子查询改为EXISTS（子查询大时）
- 确保子查询的关联字段有索引
- 使用派生表时，考虑物化为临时表
10.4 CTE和派生表的区别？
答案：
- CTE使用WITH定义，可读性更好，可以递归
- 派生表直接在FROM中定义
- CTE可以多次引用，派生表需要重复定义
- MySQL 8.0+推荐优先使用CTE
10.5 如何实现分组TOP N？
答案：使用窗口函数
WITHrankedAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYgroup_idORDERBYscoreDESC)ASrnFROMscores)SELECT*FROMrankedWHERErn<=N;十一、总结
今天我们学习了MySQL子查询与高级查询的核心知识：
- 子查询分类：标量子查询、行子查询、表子查询
- WHERE中的子查询：IN、EXISTS、比较运算符
- FROM中的子查询：派生表的使用和优化
- SELECT中的子查询：关联子查询的性能问题
- UNION/UNION ALL：合并结果集
- 窗口函数：ROW_NUMBER、RANK、LEAD/LAG等
- CTE：公用表表达式，提高可读性
下一步预告
Day8：MySQL索引原理与优化
明天我们将进入MySQL性能优化的核心领域——索引。你将学习到B+树索引原理、索引类型、索引设计原则，以及如何通过EXPLAIN分析查询性能。索引是数据库优化的基石，掌握它，你就能让查询速度提升百倍！敬请期待！
参考资料
MySQL 8.0 Reference Manual - Subqueries
互动话题
- 你在使用子查询时遇到过哪些性能问题？是如何解决的？
- 你的MySQL版本是多少？是否已经用上了窗口函数和CTE？
- 在实际项目中，你觉得子查询和JOIN哪个更好用？
如果觉得本文对你有帮助，请点赞收藏！明天见！