本系列系统梳理了 Java 开发的详细知识点从基础语法到工程实践层层递进内容详实成体系建议先收藏再慢慢阅读方便日后随时回顾查阅。前言后端开发不只是写 Java——日常排查问题、给数据团队对账、写报表统计都离不开 SQL。尤其是广告系统里统计某个广告主最近 30 天的曝光转化率按渠道排名找出 Top 10 投放计划这类需求光靠SELECT * FROM ... WHERE ...是不够的。这篇文章整理 MySQL 里常用的函数、CTE公共表表达式和窗口函数——后者是写统计类 SQL 最关键的工具。1. 常用函数函数功能函数名使用方式返回值求时间/日期差TIMEDIFF/DATEDIFFTIMEDIFF(time1, time2)/DATEDIFF(date1, date2)时间 / 天数时间转换为秒数TIME_TO_SECTIME_TO_SEC(time)整数拼接字符串CONCATCONCAT(str1, str2, str3, ...)字符串带分隔符拼接类似 Python 的 joinCONCAT_WSCONCAT_WS(separator, str1, str2, ...)字符串求最大值 / 最小值GREATEST/LEASTGREATEST(num1, num2, ...)数字四舍五入保留小数ROUNDROUND(num, 2)浮点数格式化日期DATE_FORMATDATE_FORMAT(date, %Y-%m-%d %H:%i:%s)字符串字符串转日期STR_TO_DATESTR_TO_DATE(string, format)日期大小写转换UPPER/LOWERUPPER(str)字符串类型转换CASTCAST(expression AS target_type)目标类型返回第一个非 NULL 的值COALESCECOALESCE(expr1, expr2, ...)任意日期加减DATE_ADD/DATE_SUBDATE_ADD(date, INTERVAL n DAY)日期几个容易用错或者用法不直观的单独说明一下TIMEDIFF vs DATEDIFFDATEDIFF(date1, date2)只算天数差忽略时间部分TIMEDIFF(time1, time2)算的是时间差格式HH:MM:SS两个参数必须是同类型都是 time 或都是 datetimeSELECTDATEDIFF(2026-05-20,2026-05-18);-- 2天SELECTTIMEDIFF(2026-05-20 10:00:00,2026-05-20 08:30:00);-- 01:30:00DATE_FORMAT格式化占位符占位符含义示例%Y4 位年份2026%m2 位月份05%d2 位日期20%H24 小时制小时14%i分钟30%s秒00SELECTDATE_FORMAT(NOW(),%Y-%m-%d %H:%i:%s);-- 2026-05-20 14:30:00SELECTDATE_FORMAT(NOW(),%Y年%m月);-- 2026年05月STR_TO_DATE是反过来的操作——把字符串按指定格式解析成日期类型常用于处理日期被当成字符串存进表的历史数据SELECTSTR_TO_DATE(2026/05/20,%Y/%m/%d);-- 2026-05-20CONCAT vs CONCAT_WSCONCAT直接拼接任意一个参数是NULL整个结果就是NULLCONCAT_WSWS With Separator会自动忽略NULL参数并在非空参数之间插入分隔符SELECTCONCAT(a,NULL,b);-- NULL整体失效SELECTCONCAT_WS(-,a,NULL,b);-- a-b自动跳过 NULLCOALESCE处理 NULL 的默认值返回参数列表中第一个非NULL的值常用于给可能为空的字段一个默认值-- nickname 为空时显示 usernameusername 也为空时显示 匿名用户SELECTCOALESCE(nickname,username,匿名用户)ASdisplay_nameFROMusers;CAST类型转换SELECTCAST(123ASSIGNED);-- 123字符串转整数SELECTCAST(3.789ASDECIMAL(5,2));-- 3.79保留 2 位小数SELECTCAST(2026-05-20ASDATE);-- 2026-05-20字符串转日期DATE_ADD / DATE_SUB日期加减SELECTDATE_ADD(2026-05-20,INTERVAL7DAY);-- 2026-05-27SELECTDATE_SUB(2026-05-20,INTERVAL1MONTH);-- 2026-04-20SELECTDATE_ADD(NOW(),INTERVAL-30DAY);-- 30 天前负数 SUB2. CTE用 WITH 语句拆分查询复杂查询如果写成一层套一层的子查询会很难读、很难调试。CTECommon Table Expression公共表表达式用WITH ... AS (...)把每一步查询命名成一个临时结果集后面的查询可以直接引用它就像引用一张表WITHresultAS(SELECTMAX(DATE(start_time))ASmax_dateFROMtb_user_video_log),join_tableAS(SELECTtag,if_retweetFROMtb_user_video_logAStINNERJOINtb_video_infoASiONt.video_idi.video_id,resultWHEREDATEDIFF(result.max_date,start_time)30)SELECTtag,SUM(if_retweet)ASretweet_cnt,ROUND(SUM(if_retweet)/COUNT(*),3)ASretweet_rateFROMjoin_tableGROUPBYtagORDERBYretweet_rateDESC;逐步拆解这个例子result先算出整张日志表里最新的一天max_date——后面最近 30 天都以这一天为基准而不是NOW()因为测试数据可能不是今天的。join_table把日志表和视频信息表 join 起来拿到每条记录的tag视频标签和if_retweet是否转发。这里FROM ... , result用逗号把单行的result也接进FROM列表——因为result只有一行一列这相当于把max_date这个值广播到每一行方便在WHERE里用DATEDIFF(result.max_date, start_time) 30筛选最近 30 天的数据。最外层查询基于join_table按tag分组统计每个标签的转发数retweet_cnt和转发率retweet_rate。好处每个 CTE 都可以单独拿出来跑看中间结果对不对——比如先单独执行result确认max_date算对了再执行join_table逐步排查比一整条嵌套子查询好调试得多。3. CASE 与 IFCASE多分支条件CASEif_followWHEN2THEN-1ELSEif_followEND等价于如果if_follow等于 2结果为 -1否则结果就是if_follow本身。CASE还有不带表达式、直接写条件的形式CASEWHENscore90THEN优秀WHENscore60THEN及格ELSE不及格ENDASlevelIF单分支条件MySQL 专有函数IF(condition,true_value,false_value)IF是CASE WHEN ... THEN ... ELSE ... END的简写只能写一个条件适合简单的二选一场景SELECTIF(score60,及格,不及格)ASresultFROMexams;4. 窗口函数参考通俗易懂的学会SQL窗口函数GROUP BY分组后每组只能输出一行——明细数据没了。但很多统计需求是既要看每一行的明细又要算出这一行在所在分组里排第几名 / 和上一行比变化了多少。窗口函数就是为这类需求设计的它不会减少行数而是在每一行旁边多算一列基于某个窗口范围的统计值。窗口函数OVER(PARTITIONBY用于分组的列名ORDERBY用于排序的列名ROWS/RANGE 子句)PARTITION BY把数据分成多个窗口类似GROUP BY但不会合并行ORDER BY决定窗口内行的顺序影响排名、LAG/LEAD 等函数的结果ROWS/RANGE子句进一步限定当前窗口包含哪些行默认是整个分区窗口函数原则上只能写在SELECT子句中。4.1 专用窗口函数排名类函数行为示例值相同时ROW_NUMBER()为每一行分配唯一的连续序号不管值是否相同1, 2, 3, 4RANK()相同值排名相同之后的排名会跳号1, 1, 3, 4DENSE_RANK()相同值排名相同之后的排名不跳号1, 1, 2, 3NTILE(n)把结果集尽量平均分成 n 组返回每行所在的组号8 行分 4 组 → 1,1,2,2,3,3,4,4SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrow_num,RANK()OVER(ORDERBYscoreDESC)ASrnk,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdense_rnkFROMexams;namescorerow_numrnkdense_rnkAlice90111Bob90211Carol85332Dave804434.2 前后行函数LAG / LEAD用于在当前行直接拿到上一行或下一行的值常用来计算环比比如本月销量 - 上月销量函数作用示例LAG(column, offset, default)访问当前行之前的第offset行LAG(sales, 1, 0) OVER(PARTITION BY product ORDER BY month)LEAD(column, offset, default)访问当前行之后的第offset行LEAD(price, 1) OVER(ORDER BY date)SELECTproduct,month,sales,LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASlast_month_sales,sales-LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASdiffFROMmonthly_sales;offset是往前/往后数几行最常用的是1上一行/下一行default是如果取不到比如第一行没有上一行该返回什么这里传0表示第一个月的环比基准是 04.3 首尾值函数函数作用FIRST_VALUE(column)返回窗口框架中的第一个值LAST_VALUE(column)返回窗口框架中的最后一个值NTH_VALUE(column, n)返回窗口框架中的第 n 个值SELECTname,dept,salary,FIRST_VALUE(salary)OVER(PARTITIONBYdeptORDERBYhire_date)ASfirst_hired_salary,NTH_VALUE(salary,3)OVER(PARTITIONBYdeptORDERBYsalaryDESC)ASthird_highest_salaryFROMemployees;LAST_VALUE的坑默认的窗口框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区第一行到当前行所以LAST_VALUE默认取到的其实是当前行不是整个分区的最后一行——想拿到分区真正的最后一行必须显式指定ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING见 4.5 节。4.4 聚合窗口函数SUM/AVG/COUNT/MIN/MAX加上OVER()就变成窗口函数——区别于GROUP BY里的聚合函数它不会把多行合并成一行SELECTname,dept,salary,SUM(salary)OVER(PARTITIONBYdept)ASdept_total,AVG(salary)OVER(PARTITIONBYdept)ASdept_avg,salary/SUM(salary)OVER(PARTITIONBYdept)ASsalary_ratioFROMemployees;每一行都能看到自己的明细name、salary同时又能看到所在部门的总和、平均值以及自己占部门总额的比例——这是GROUP BY做不到的。4.5 窗口框架规范ROWS BETWEENROWS BETWEEN ... AND ...进一步限定当前窗口具体包含哪些行关键字含义UNBOUNDED PRECEDING分区第一行UNBOUNDED FOLLOWING分区最后一行CURRENT ROW当前行n PRECEDING当前行往前数第 n 行n FOLLOWING当前行往后数第 n 行例 1累计求和从分区第一行累加到当前行SELECTid,category,sales_volume,SUM(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_totalFROMsales;例 2移动平均前一行 当前行 后一行共 3 行的平均值SELECTid,category,sales_volume,AVG(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMsales;4.6 为什么有时必须用窗口函数代替 GROUP BY下面这条 SQL 想算每个商品的利润率——profit_price 1 - 成本价 / 加权平均售价-- 报错in_price 既不在 GROUP BY 里也没被聚合函数包裹product_profitAS(SELECTproduct_id,1-in_price/(SUM(price*cnt)/SUM(cnt))ASprofit_priceFROMjoin_tbGROUPBYproduct_id)为什么报错SQL 规定SELECT里出现的每一列要么在GROUP BY里要么被聚合函数SUM/MAX/…包裹。这里in_price两者都不满足——虽然同一个product_id对应的in_price实际上是同一个值成本价不会按行变化但数据库不知道这件事仍然会报错。解决方案把聚合从按组合并换成窗口聚合这样既能用到SUM(...) OVER(PARTITION BY product_id)算出整个分组的汇总值又能保留in_price这种逐行字段不需要把它也塞进GROUP BYproduct_profitAS(SELECTDISTINCTproduct_id,1-(in_price/(SUM(price*cnt)OVER(PARTITIONBYproduct_id)/SUM(cnt)OVER(PARTITIONBYproduct_id)))ASprofit_priceFROMjoin_tb)SUM(...) OVER(PARTITION BY product_id)会把该product_id分组内price*cnt的总和算出来贴在每一行上不合并行所以in_price这种逐行字段可以直接拿来用。算完之后每个product_id会有多行结果一行对应原表的一行值都一样最后用SELECT DISTINCT去重成一个商品一行。5. 小结主题关键要点常用函数DATE_FORMAT/STR_TO_DATE互为反操作CONCAT_WS自动跳过NULLCOALESCE取第一个非空值CTEWITH ... AS (...)把复杂查询拆成可单独调试的步骤逗号 单行 CTE 可以把一个值广播到每一行CASE / IFCASE支持多分支IF是二选一的简写排名窗口函数ROW_NUMBER不重复、RANK重复后跳号、DENSE_RANK重复后不跳号、NTILE分桶LAG / LEAD取上一行/下一行的值常用于环比计算聚合窗口函数SUM/AVG/COUNT OVER(PARTITION BY ...)不合并行每行都能看到分组汇总ROWS BETWEEN限定窗口范围累计求和用UNBOUNDED PRECEDING AND CURRENT ROW移动平均用n PRECEDING AND n FOLLOWING窗口函数 vs GROUP BY当SELECT里要混用逐行字段和分组聚合值时窗口函数 SELECT DISTINCT比GROUP BY更合适下一篇预告MySQL 原理与优化——存储引擎、索引、锁与事务隔离级别 如果这篇文章对你有帮助别忘了点赞、收藏、关注三连关注我让你在 Java 学习的道路上不迷路持续为你带来成体系的 Java 干货~
Java基础(23) | SQL 进阶语法：常用函数、CTE 与窗口函数
本系列系统梳理了 Java 开发的详细知识点从基础语法到工程实践层层递进内容详实成体系建议先收藏再慢慢阅读方便日后随时回顾查阅。前言后端开发不只是写 Java——日常排查问题、给数据团队对账、写报表统计都离不开 SQL。尤其是广告系统里统计某个广告主最近 30 天的曝光转化率按渠道排名找出 Top 10 投放计划这类需求光靠SELECT * FROM ... WHERE ...是不够的。这篇文章整理 MySQL 里常用的函数、CTE公共表表达式和窗口函数——后者是写统计类 SQL 最关键的工具。1. 常用函数函数功能函数名使用方式返回值求时间/日期差TIMEDIFF/DATEDIFFTIMEDIFF(time1, time2)/DATEDIFF(date1, date2)时间 / 天数时间转换为秒数TIME_TO_SECTIME_TO_SEC(time)整数拼接字符串CONCATCONCAT(str1, str2, str3, ...)字符串带分隔符拼接类似 Python 的 joinCONCAT_WSCONCAT_WS(separator, str1, str2, ...)字符串求最大值 / 最小值GREATEST/LEASTGREATEST(num1, num2, ...)数字四舍五入保留小数ROUNDROUND(num, 2)浮点数格式化日期DATE_FORMATDATE_FORMAT(date, %Y-%m-%d %H:%i:%s)字符串字符串转日期STR_TO_DATESTR_TO_DATE(string, format)日期大小写转换UPPER/LOWERUPPER(str)字符串类型转换CASTCAST(expression AS target_type)目标类型返回第一个非 NULL 的值COALESCECOALESCE(expr1, expr2, ...)任意日期加减DATE_ADD/DATE_SUBDATE_ADD(date, INTERVAL n DAY)日期几个容易用错或者用法不直观的单独说明一下TIMEDIFF vs DATEDIFFDATEDIFF(date1, date2)只算天数差忽略时间部分TIMEDIFF(time1, time2)算的是时间差格式HH:MM:SS两个参数必须是同类型都是 time 或都是 datetimeSELECTDATEDIFF(2026-05-20,2026-05-18);-- 2天SELECTTIMEDIFF(2026-05-20 10:00:00,2026-05-20 08:30:00);-- 01:30:00DATE_FORMAT格式化占位符占位符含义示例%Y4 位年份2026%m2 位月份05%d2 位日期20%H24 小时制小时14%i分钟30%s秒00SELECTDATE_FORMAT(NOW(),%Y-%m-%d %H:%i:%s);-- 2026-05-20 14:30:00SELECTDATE_FORMAT(NOW(),%Y年%m月);-- 2026年05月STR_TO_DATE是反过来的操作——把字符串按指定格式解析成日期类型常用于处理日期被当成字符串存进表的历史数据SELECTSTR_TO_DATE(2026/05/20,%Y/%m/%d);-- 2026-05-20CONCAT vs CONCAT_WSCONCAT直接拼接任意一个参数是NULL整个结果就是NULLCONCAT_WSWS With Separator会自动忽略NULL参数并在非空参数之间插入分隔符SELECTCONCAT(a,NULL,b);-- NULL整体失效SELECTCONCAT_WS(-,a,NULL,b);-- a-b自动跳过 NULLCOALESCE处理 NULL 的默认值返回参数列表中第一个非NULL的值常用于给可能为空的字段一个默认值-- nickname 为空时显示 usernameusername 也为空时显示 匿名用户SELECTCOALESCE(nickname,username,匿名用户)ASdisplay_nameFROMusers;CAST类型转换SELECTCAST(123ASSIGNED);-- 123字符串转整数SELECTCAST(3.789ASDECIMAL(5,2));-- 3.79保留 2 位小数SELECTCAST(2026-05-20ASDATE);-- 2026-05-20字符串转日期DATE_ADD / DATE_SUB日期加减SELECTDATE_ADD(2026-05-20,INTERVAL7DAY);-- 2026-05-27SELECTDATE_SUB(2026-05-20,INTERVAL1MONTH);-- 2026-04-20SELECTDATE_ADD(NOW(),INTERVAL-30DAY);-- 30 天前负数 SUB2. CTE用 WITH 语句拆分查询复杂查询如果写成一层套一层的子查询会很难读、很难调试。CTECommon Table Expression公共表表达式用WITH ... AS (...)把每一步查询命名成一个临时结果集后面的查询可以直接引用它就像引用一张表WITHresultAS(SELECTMAX(DATE(start_time))ASmax_dateFROMtb_user_video_log),join_tableAS(SELECTtag,if_retweetFROMtb_user_video_logAStINNERJOINtb_video_infoASiONt.video_idi.video_id,resultWHEREDATEDIFF(result.max_date,start_time)30)SELECTtag,SUM(if_retweet)ASretweet_cnt,ROUND(SUM(if_retweet)/COUNT(*),3)ASretweet_rateFROMjoin_tableGROUPBYtagORDERBYretweet_rateDESC;逐步拆解这个例子result先算出整张日志表里最新的一天max_date——后面最近 30 天都以这一天为基准而不是NOW()因为测试数据可能不是今天的。join_table把日志表和视频信息表 join 起来拿到每条记录的tag视频标签和if_retweet是否转发。这里FROM ... , result用逗号把单行的result也接进FROM列表——因为result只有一行一列这相当于把max_date这个值广播到每一行方便在WHERE里用DATEDIFF(result.max_date, start_time) 30筛选最近 30 天的数据。最外层查询基于join_table按tag分组统计每个标签的转发数retweet_cnt和转发率retweet_rate。好处每个 CTE 都可以单独拿出来跑看中间结果对不对——比如先单独执行result确认max_date算对了再执行join_table逐步排查比一整条嵌套子查询好调试得多。3. CASE 与 IFCASE多分支条件CASEif_followWHEN2THEN-1ELSEif_followEND等价于如果if_follow等于 2结果为 -1否则结果就是if_follow本身。CASE还有不带表达式、直接写条件的形式CASEWHENscore90THEN优秀WHENscore60THEN及格ELSE不及格ENDASlevelIF单分支条件MySQL 专有函数IF(condition,true_value,false_value)IF是CASE WHEN ... THEN ... ELSE ... END的简写只能写一个条件适合简单的二选一场景SELECTIF(score60,及格,不及格)ASresultFROMexams;4. 窗口函数参考通俗易懂的学会SQL窗口函数GROUP BY分组后每组只能输出一行——明细数据没了。但很多统计需求是既要看每一行的明细又要算出这一行在所在分组里排第几名 / 和上一行比变化了多少。窗口函数就是为这类需求设计的它不会减少行数而是在每一行旁边多算一列基于某个窗口范围的统计值。窗口函数OVER(PARTITIONBY用于分组的列名ORDERBY用于排序的列名ROWS/RANGE 子句)PARTITION BY把数据分成多个窗口类似GROUP BY但不会合并行ORDER BY决定窗口内行的顺序影响排名、LAG/LEAD 等函数的结果ROWS/RANGE子句进一步限定当前窗口包含哪些行默认是整个分区窗口函数原则上只能写在SELECT子句中。4.1 专用窗口函数排名类函数行为示例值相同时ROW_NUMBER()为每一行分配唯一的连续序号不管值是否相同1, 2, 3, 4RANK()相同值排名相同之后的排名会跳号1, 1, 3, 4DENSE_RANK()相同值排名相同之后的排名不跳号1, 1, 2, 3NTILE(n)把结果集尽量平均分成 n 组返回每行所在的组号8 行分 4 组 → 1,1,2,2,3,3,4,4SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrow_num,RANK()OVER(ORDERBYscoreDESC)ASrnk,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdense_rnkFROMexams;namescorerow_numrnkdense_rnkAlice90111Bob90211Carol85332Dave804434.2 前后行函数LAG / LEAD用于在当前行直接拿到上一行或下一行的值常用来计算环比比如本月销量 - 上月销量函数作用示例LAG(column, offset, default)访问当前行之前的第offset行LAG(sales, 1, 0) OVER(PARTITION BY product ORDER BY month)LEAD(column, offset, default)访问当前行之后的第offset行LEAD(price, 1) OVER(ORDER BY date)SELECTproduct,month,sales,LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASlast_month_sales,sales-LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASdiffFROMmonthly_sales;offset是往前/往后数几行最常用的是1上一行/下一行default是如果取不到比如第一行没有上一行该返回什么这里传0表示第一个月的环比基准是 04.3 首尾值函数函数作用FIRST_VALUE(column)返回窗口框架中的第一个值LAST_VALUE(column)返回窗口框架中的最后一个值NTH_VALUE(column, n)返回窗口框架中的第 n 个值SELECTname,dept,salary,FIRST_VALUE(salary)OVER(PARTITIONBYdeptORDERBYhire_date)ASfirst_hired_salary,NTH_VALUE(salary,3)OVER(PARTITIONBYdeptORDERBYsalaryDESC)ASthird_highest_salaryFROMemployees;LAST_VALUE的坑默认的窗口框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区第一行到当前行所以LAST_VALUE默认取到的其实是当前行不是整个分区的最后一行——想拿到分区真正的最后一行必须显式指定ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING见 4.5 节。4.4 聚合窗口函数SUM/AVG/COUNT/MIN/MAX加上OVER()就变成窗口函数——区别于GROUP BY里的聚合函数它不会把多行合并成一行SELECTname,dept,salary,SUM(salary)OVER(PARTITIONBYdept)ASdept_total,AVG(salary)OVER(PARTITIONBYdept)ASdept_avg,salary/SUM(salary)OVER(PARTITIONBYdept)ASsalary_ratioFROMemployees;每一行都能看到自己的明细name、salary同时又能看到所在部门的总和、平均值以及自己占部门总额的比例——这是GROUP BY做不到的。4.5 窗口框架规范ROWS BETWEENROWS BETWEEN ... AND ...进一步限定当前窗口具体包含哪些行关键字含义UNBOUNDED PRECEDING分区第一行UNBOUNDED FOLLOWING分区最后一行CURRENT ROW当前行n PRECEDING当前行往前数第 n 行n FOLLOWING当前行往后数第 n 行例 1累计求和从分区第一行累加到当前行SELECTid,category,sales_volume,SUM(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_totalFROMsales;例 2移动平均前一行 当前行 后一行共 3 行的平均值SELECTid,category,sales_volume,AVG(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMsales;4.6 为什么有时必须用窗口函数代替 GROUP BY下面这条 SQL 想算每个商品的利润率——profit_price 1 - 成本价 / 加权平均售价-- 报错in_price 既不在 GROUP BY 里也没被聚合函数包裹product_profitAS(SELECTproduct_id,1-in_price/(SUM(price*cnt)/SUM(cnt))ASprofit_priceFROMjoin_tbGROUPBYproduct_id)为什么报错SQL 规定SELECT里出现的每一列要么在GROUP BY里要么被聚合函数SUM/MAX/…包裹。这里in_price两者都不满足——虽然同一个product_id对应的in_price实际上是同一个值成本价不会按行变化但数据库不知道这件事仍然会报错。解决方案把聚合从按组合并换成窗口聚合这样既能用到SUM(...) OVER(PARTITION BY product_id)算出整个分组的汇总值又能保留in_price这种逐行字段不需要把它也塞进GROUP BYproduct_profitAS(SELECTDISTINCTproduct_id,1-(in_price/(SUM(price*cnt)OVER(PARTITIONBYproduct_id)/SUM(cnt)OVER(PARTITIONBYproduct_id)))ASprofit_priceFROMjoin_tb)SUM(...) OVER(PARTITION BY product_id)会把该product_id分组内price*cnt的总和算出来贴在每一行上不合并行所以in_price这种逐行字段可以直接拿来用。算完之后每个product_id会有多行结果一行对应原表的一行值都一样最后用SELECT DISTINCT去重成一个商品一行。5. 小结主题关键要点常用函数DATE_FORMAT/STR_TO_DATE互为反操作CONCAT_WS自动跳过NULLCOALESCE取第一个非空值CTEWITH ... AS (...)把复杂查询拆成可单独调试的步骤逗号 单行 CTE 可以把一个值广播到每一行CASE / IFCASE支持多分支IF是二选一的简写排名窗口函数ROW_NUMBER不重复、RANK重复后跳号、DENSE_RANK重复后不跳号、NTILE分桶LAG / LEAD取上一行/下一行的值常用于环比计算聚合窗口函数SUM/AVG/COUNT OVER(PARTITION BY ...)不合并行每行都能看到分组汇总ROWS BETWEEN限定窗口范围累计求和用UNBOUNDED PRECEDING AND CURRENT ROW移动平均用n PRECEDING AND n FOLLOWING窗口函数 vs GROUP BY当SELECT里要混用逐行字段和分组聚合值时窗口函数 SELECT DISTINCT比GROUP BY更合适下一篇预告MySQL 原理与优化——存储引擎、索引、锁与事务隔离级别 如果这篇文章对你有帮助别忘了点赞、收藏、关注三连关注我让你在 Java 学习的道路上不迷路持续为你带来成体系的 Java 干货~
