写在前面大家好欢迎来到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 ALL5.1 UNION vs UNION ALL5.2 UNION注意事项六、窗口函数MySQL 8.06.1 窗口函数语法6.2 排名函数6.3 分组排名6.4 偏移函数LEAD/LAG6.5 聚合窗口函数七、CTE公用表表达式WITH子句7.1 基本CTE7.2 多个CTE7.3 递归CTE八、实战复杂业务查询场景8.1 连续登录天数统计8.2 分组TOP N8.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_idoi.order_idJOINproducts pONoi.product_idp.product_idWHEREp.category_id1);2.2 EXISTS子查询EXISTS用于判断子查询是否返回结果返回布尔值。-- 查询有订单的用户与IN效果相同SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.user_id);-- 查询没有订单的用户SELECT*FROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_idu.user_id);IN vs EXISTS对比特性INEXISTS执行方式先执行子查询相关子查询逐行判断性能子查询结果小时快子查询结果大时快NULL处理需注意NULL值不受NULL影响使用场景固定值列表关联条件复杂时2.3 比较运算符子查询-- 查询订单金额大于平均订单金额的记录SELECT*FROMordersWHEREtotal_amount(SELECTAVG(total_amount)FROMorders);-- 查询每个用户超过其平均订单金额的订单SELECTo1.*FROMorders o1WHEREo1.total_amount(SELECTAVG(o2.total_amount)FROMorders o2WHEREo2.user_ido1.user_id);2.4 ALL和ANY/SOME-- 查询订单金额大于所有用户平均消费的用户ALLSELECT*FROMusers uWHEREtotal_spentALL(SELECTAVG(total_amount)FROMordersGROUPBYuser_id);-- 查询订单金额大于任意一个用户平均消费的用户ANYSELECT*FROMusers uWHEREtotal_spentANY(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_idt.user_id;3.1 派生表优化MySQL 5.7会对派生表进行合并优化-- MySQL会自动将派生表合并到外层查询SELECT*FROM(SELECT*FROMordersWHEREstatuscompleted)ASoWHEREo.amount100;-- 等同于SELECT*FROMordersWHEREstatuscompletedANDamount100;四、SELECT中的子查询关联子查询SELECT中的子查询会为每一行执行一次称为关联子查询。-- 查询用户及其订单数量SELECTu.user_id,u.username,(SELECTCOUNT(*)FROMorders oWHEREo.user_idu.user_id)ASorder_countFROMusers u;-- 查询每个订单的用户消费排名SELECTo.order_id,o.user_id,o.total_amount,(SELECTCOUNT(*)1FROMorders o2WHEREo2.user_ido.user_idANDo2.total_amounto.total_amount)ASrank_in_userFROMorders o;踩坑提醒SELECT中的关联子查询性能较差每行都要执行一次子查询。大数据量时建议使用JOIN替代。五、UNION和UNION ALLUNION用于合并多个SELECT语句的结果集。5.1 UNION vs UNION ALL特性UNIONUNION ALL去重自动去重不去重性能较慢需要比较较快使用场景需要唯一结果允许重复结果-- UNION去重SELECTcityFROMusersWHEREage30UNIONSELECTcityFROMusersWHEREtotal_spent1000;-- UNION ALL保留所有记录SELECTVIPASuser_type,user_id,usernameFROMusersWHEREtotal_spent10000UNIONALLSELECT普通,user_id,usernameFROMusersWHEREtotal_spent10000;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_idusernametotal_spentrow_numrank_numdense_rank_num1张三100001112李四80002223王五80003224赵六50004436.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子句CTECommon 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_idus.user_id;7.2 多个CTEWITHorder_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_idos.user_idLEFTJOINpayment_stats psONu.user_idps.user_id;7.3 递归CTE递归CTE用于处理层级数据-- 查询分类的所有子分类WITHRECURSIVE category_treeAS(-- 锚成员起始点SELECTcategory_id,category_name,parent_id,0ASlevelFROMcategoriesWHEREcategory_id1-- 从根分类开始UNIONALL-- 递归成员SELECTc.category_id,c.category_name,c.parent_id,ct.level1FROMcategories cJOINcategory_tree ctONc.parent_idct.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_productsWHERErnk3;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_iduc.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_idu.user_id)AScntFROMusers u;-- 高效写法使用JOINSELECTu.*,COALESCE(o.cnt,0)AScntFROMusers uLEFTJOIN(SELECTuser_id,COUNT(*)AScntFROMordersGROUPBYuser_id)oONu.user_ido.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_idu.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推荐优先使用CTE10.5 如何实现分组TOP N答案使用窗口函数WITHrankedAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYgroup_idORDERBYscoreDESC)ASrnFROMscores)SELECT*FROMrankedWHERErnN;十一、总结今天我们学习了MySQL子查询与高级查询的核心知识子查询分类标量子查询、行子查询、表子查询WHERE中的子查询IN、EXISTS、比较运算符FROM中的子查询派生表的使用和优化SELECT中的子查询关联子查询的性能问题UNION/UNION ALL合并结果集窗口函数ROW_NUMBER、RANK、LEAD/LAG等CTE公用表表达式提高可读性下一步预告Day8MySQL索引原理与优化明天我们将进入MySQL性能优化的核心领域——索引。你将学习到B树索引原理、索引类型、索引设计原则以及如何通过EXPLAIN分析查询性能。索引是数据库优化的基石掌握它你就能让查询速度提升百倍敬请期待参考资料MySQL 8.0 Reference Manual - Subqueries互动话题你在使用子查询时遇到过哪些性能问题是如何解决的你的MySQL版本是多少是否已经用上了窗口函数和CTE在实际项目中你觉得子查询和JOIN哪个更好用如果觉得本文对你有帮助请点赞收藏明天见
