先说说我为什么想写这篇。前两天帮同事审一段统计报表的SQL发现他把聚合函数、分组和联合查询这三件事混在一起用结果查出来的数据怎么看怎么不对。MySQL里这三块确实是基础中的基础但越基础的东西翻起车来越让人头疼有人把COUNT(*)当成COUNT(列名)用有人GROUP BY之后SELECT一个非聚合列然后数据随机还有人UNION和UNION ALL搞混导致线上数据直接翻倍。这篇文章我不讲安装、不讲客户端工具就踏踏实实把聚合函数、分组、联合查询这三件事从原理到实操捋一遍。最后用一个销售统计的完整案例把三个知识点串成一条线你照着跑一遍基本就能把这套东西吃透。1. 聚合函数先搞清楚每一类函数到底在算什么1.1 COUNT、SUM、AVG、MAX、MIN各自的NULL语义聚合函数就是把多行数据压缩成一行结果的函数。COUNT、SUM、AVG、MAX、MIN是五个最基础的但它们的NULL处理逻辑完全不同这个不搞清楚统计结果就是错的。COUNT(*)统计的是行数不管这一行里有没有NULL。COUNT(列名)统计的是这个列非NULL值的个数。举个例子一张订单表有100条记录其中50条的pay_time是NULL那么COUNT(*)返回100COUNT(pay_time)返回50。面试里经常问COUNT(*)和COUNT(1)的区别其实在MySQL 5.7的优化器里两者性能基本没差别真正有差别的是COUNT(列名)——它要额外判断NULL。SUM和AVG会自动忽略NULL。但这里有个隐蔽的坑如果参与计算的行全部都是NULL或者整个分组里一行都没有那SUM返回的是NULL而不是0。我之前写过一条SQL取某部门订单金额总和结果页面上显示空白——不是没数据是返回了NULL。解决办法是包一层COALESCE(SUM(amount), 0)。AVG也一样它会忽略NULL行再算平均值如果你想把NULL当成0参与计算得先AVG(COALESCE(amount, 0))。MAX和MIN除了数值对字符串也能用按字典序比较。比如MIN(employee_name)可以取出员工姓氏字母最靠前的那一个这在某些场景下还挺有用但大部分报表场景还是对数值和日期用。1.2 条件聚合一个CASE WHEN搞定带条件的统计报表里最常遇到的需求是统计已支付的订单总金额或者统计男性员工数量这类带条件的聚合。新手会写WHERE pay_status 1然后SUM(amount)但如果你要同时统计已支付和未支付、或者统计多个条件下的数值一条SQL里就要用条件聚合SELECT dept_id, COUNT(*) AS total_cnt, SUM(CASE WHEN pay_status 1 THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN pay_status 0 THEN amount ELSE 0 END) AS unpaid_amount FROM order_record GROUP BY dept_id;这里COUNT(CASE WHEN pay_status 1 THEN 1 END)也等价于条件计数因为COUNT会忽略NULL而不满足条件时CASE返回值是NULL不写ELSE默认就是NULL。这是把WHERE过滤逻辑放进聚合函数里的标准姿势实际工作中用的频率极高。1.3 GROUP_CONCAT把一组值拼成字符串以及截断的坑GROUP_CONCAT是聚合函数里的另类它不计算数值而是把组内的值拼成一个字符串。比如每个部门有哪些员工SELECT dept_id, GROUP_CONCAT(employee_name ORDER BY employee_id SEPARATOR 、) FROM employee GROUP BY dept_id;默认用逗号分隔可以指定SEPARATOR也可以ORDER BY控制拼接顺序。但GROUP_CONCAT有个经典深坑结果长度受group_concat_max_len限制默认只有1024字节。一旦拼接结果超过这个值后面的内容会被静默截断而且不报错。我第一次遇到的时候排查了快一个小时以为数据出了问题最后发现是拼接的备注字段太长被截了。解决方式是调大会话级变量SET SESSION group_concat_max_len 102400;如果是线上系统建议在连接初始化时统一设置或者在配置文件里调整。这个坑在文档里其实写得很清楚但实际遇到的人十个里面有八个是不知道的。2. GROUP BY分组的执行逻辑以及那些容易翻车的细节2.1 SQL执行顺序为什么GROUP BY能看到WHERE过滤后的数据分组不是把相同值归拢这么简单理解GROUP BY必须理解SQL的执行顺序。一条查询语句的完整逻辑顺序大致是FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT注意看WHERE在GROUP BY之前执行。这意味着GROUP BY看到的数据已经是WHERE过滤之后的数据。这解释了很多人困惑的一个点——为什么WHERE里不能用聚合函数因为WHERE执行的时候分组都还没发生聚合函数根本没有东西可以算。SELECT列也在GROUP BY之后才计算所以SELECT里的聚合表达式才能在分组结果上工作。理解这个顺序很多为什么报错的问题不用查资料你都能猜出来。2.2 ONLY_FULL_GROUP_BY模式不按规矩来就是随机结果MySQL 5.7.5之后默认开启了ONLY_FULL_GROUP_BYSQL模式。这个模式的要求是SELECT、HAVING、ORDER BY里出现的非聚合列必须出现在GROUP BY中或者被聚合函数包裹或者功能上依赖于GROUP BY的列。什么意思呢直接看反例-- 错误示范name既不在GROUP BY里也没被聚合函数包裹 SELECT dept_id, employee_name, COUNT(*) FROM employee GROUP BY dept_id;在关掉ONLY_FULL_GROUP_BY的老版本MySQL或者手动关闭了里这条SQL能跑employee_name返回的是该分组内MySQL实际读到的第一行的值。这个值完全不确定取决于存储引擎怎么返回数据、有没有索引、数据分布怎样——今天跑是这个值明天可能就变了。这种随机性特别坑尤其是你以为自己在查部门下任意一个员工名实际上结果根本不可控。反过来如果GROUP BY的是主键那SELECT主键对应的其他字段是允许的因为主键能够唯一确定这一行的其他列这在SQL标准里叫函数依赖。比如-- 合法id是主键name功能依赖于id SELECT id, name, COUNT(*) FROM employee GROUP BY id;这在实际业务里很有用但很多人在ONLY_FULL_GROUP_BY下会困惑为什么有时候报错有时候不报错其实就是这个函数依赖规则在起作用。2.3 多列分组与组内排序的真相多列分组很直观GROUP BY dept_id, position就是先在部门维度分组再在部门内按职位再分。报表里部门×职级、年份×月份都是典型的多列分组场景。但提一个容易忽略的点MySQL 8.0移除了GROUP BY的隐式排序。在5.7及更早版本里GROUP BY dept_id的结果默认会按dept_id升序排列。到了8.0这个行为没了如果你希望结果有序必须显式加ORDER BY。做版本升级的同学代码里如果依赖了GROUP BY的默认排序上线前一定要检查否则报表顺序会乱。至于分组后取每组的前几条GROUP BY本身做不到。这类问题要交给窗口函数SELECT dept_id, employee_name, salary FROM ( SELECT dept_id, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 3;窗口函数虽然不算是聚合函数但它和GROUP BY配合出现的频率极高面试也常考这里一并提一下。3. WHERE与HAVING分水岭在分组之前还是分组之后3.1 执行时机不同语义就完全不同WHERE和HAVING都能写过滤条件但一个是在分组前过滤行一个是分组后过滤组。这个执行时机差异决定了你能在条件里写什么对比项WHEREHAVING执行时机分组之前分组之后能否使用聚合函数不能能过滤对象行组能否用SELECT别名不能MySQL中可以典型用途WHERE status 1HAVING COUNT(*) 5最常见的报错就是写WHERE COUNT(*) 5。原因前面说过——WHERE执行时还没分组COUNT(*)根本无从计算。而HAVING是在分组完成后对每个组的结果集做过滤所以可以写HAVING COUNT(*) 5。3.2 能用WHERE过滤的绝不拖到HAVING从性能角度看WHERE和HAVING的差距往往比想象中大。WHERE过滤发生在分组之前可以直接缩小参与分组的数据集而且如果过滤列上有索引数据库可以走索引快速筛选。HAVING则必须等全部分组和聚合计算完成之后再对聚合结果做筛选——这意味着分组和聚合的计算量一点都不会减少。比如说统计每个部门已支付订单总数大于10的部门两种写法-- 写法一先用WHERE过滤掉未支付订单推荐 SELECT dept_id, COUNT(*) AS paid_cnt FROM order_record WHERE pay_status 1 GROUP BY dept_id HAVING paid_cnt 10; -- 写法二用条件聚合把过滤放到HAVING SELECT dept_id, COUNT(CASE WHEN pay_status 1 THEN 1 END) AS paid_cnt FROM order_record GROUP BY dept_id HAVING paid_cnt 10;两种写法结果一样但写法一在分组前就把未支付的订单排除了写法二则把全部订单都捞进分组计算里。数据量小感觉不出来百万级订单表上性能差距可以到好几倍。原则就一条能在WHERE干的活别留给HAVING。3.3 HAVING别名和ORDER BY别名的MySQL特性很多数据库不支持在HAVING和ORDER BY里引用SELECT里的别名但MySQL支持。这是MySQL的便利之处也是从其他数据库迁移过来的人容易踩的坑在其他库可能直接报错。利用这个特性可以让SQL简洁不少SELECT dept_id, COUNT(*) AS cnt FROM order_record GROUP BY dept_id HAVING cnt 10 ORDER BY cnt DESC;但注意WHERE里不能这样用。WHERE cnt 10会报Unknown column cnt——因为WHERE执行时SELECT还没计算出cnt呢。4. 联合查询UNION与UNION ALL纵向合并的正确打开方式4.1 UNION和UNION ALL的本质区别一个去重一个不去重先澄清一个概念MySQL里的联合查询指的就是UNION和UNION ALL它们是纵向合并——把多个SELECT的结果上下摞在一起。这和JOIN横向连接列拼接是两回事。UNION会对最终结果做去重UNION ALL不做任何去重处理。这个差异直接反映在性能上UNION为了去重通常需要在临时表上做排序或建唯一索引数据量大时开销很高UNION ALL只是简单拼接基本没有额外代价。实际的业务场景里如果两条SQL查询的维度互补、不存在重复行务必要用UNION ALL。比如统计本月新增订单数和本月关闭订单数两个分组的数据天然不会重叠用UNION纯粹是浪费计算资源。SELECT new_orders AS biz_type, COUNT(*) AS cnt FROM order_record WHERE create_time 2024-11-01 AND status created UNION ALL SELECT closed_orders, COUNT(*) FROM order_record WHERE create_time 2024-11-01 AND status closed;4.2 列对齐、类型兼容和ORDER BY的三个细节使用UNION有一堆硬性规则违反任何一个都会报错或者得到意外结果。第一每个SELECT的列数必须一致。第一个SELECT有3列后面每个SELECT也必须恰好3列否则报错。第二列名以第一个SELECT为准后面的列名会被忽略。第三列的数据类型不要求完全一致但必须能隐式转换比如INT和VARCHAR可以合并但INT和DATETIME直接合并就容易乱了。还有一个非常容易踩的坑子查询里的ORDER BY会被忽略。比如-- 意图先对A表按时间排序再拼接B表 SELECT * FROM table_a ORDER BY create_time UNION ALL SELECT * FROM table_b;这条SQL里table_a的ORDER BY会被优化器直接丢掉数据库不会保证前半段结果有序。要让排序生效必须用括号包起来且加上LIMIT(SELECT * FROM table_a ORDER BY create_time LIMIT 1000) UNION ALL (SELECT * FROM table_b LIMIT 1000) ORDER BY create_time;从MySQL 8.0开始MySQL支持对单个查询块加括号并配合ORDER BY和LIMIT。注意ORDER BY create_time放在整个UNION的最后是对整个合并后的结果集排序。4.3 联合查询和聚合分组的配合场景UNION最常见的应用场景就是把多个维度统计的结果拼到一张结果集里方便一次性导出或者在前端一个表格里展示。比如报表里需要同时展示按部门统计的订单数和按月份统计的订单数一条SQL用括号包住两个分组查询再用UNION ALL拼接即可。另外联合查询配合去重也有妙用如果你需要把两张表里的客户ID合并成一个去重列表比如会员散客的表结构不同但要合并统计总数UNION天然就是一个去重工具。此时用UNION而不是UNION ALL就是正确选择因为去重本身就是需求。5. 一个销售统计需求把聚合、分组、联合查询串起来做一遍5.1 需求与建表理论说再多不如跑一遍。我设计一个贴近实际的需求把前面所有知识点串起来。假设有一张订单记录表结构如下CREATE TABLE order_record ( id BIGINT PRIMARY KEY AUTO_INCREMENT, dept_id INT NOT NULL COMMENT 部门ID, order_amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, pay_status TINYINT NOT NULL DEFAULT 0 COMMENT 支付状态0未支付1已支付, create_time DATETIME NOT NULL COMMENT 下单时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;需求是这样管理层要一张统计报表按部门维度展示订单总数、已支付总金额、最大单笔金额只要订单总数超过10笔的部门按已支付总金额降序排列同时还要有一行全公司合计最后把部门维度和月份维度两张结果合并到一个结果集方便导出Excel做双维度对比。5.2 从聚合分组到ROLLUP的渐进实现第一步先按部门分组统计基础指标。SELECT dept_id, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY dept_id;这里用到了条件聚合把已支付总金额和订单总数在一次分组里搞定。MAX(order_amount)取该部门下所有订单中金额最大的那一笔。第二步加上HAVING过滤只保留订单总数超过10笔的部门SELECT dept_id, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY dept_id HAVING total_orders 10 ORDER BY paid_amount DESC;注意HAVING total_orders 10直接用了SELECT里的别名total_orders这是MySQL允许的写法。ORDER BY paid_amount DESC实现了核心排序需求。第三步解决全公司合计这一行。新手最容易犯的错误是再写一条不带GROUP BY的聚合SQL然后用UNION ALL拼上去。其实MySQL提供了更优雅的WITH ROLLUP——在GROUP BY后面加上它MySQL会自动在结果集末尾生成一行总计这行总计中dept_id为NULLSELECT dept_id, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY dept_id WITH ROLLUP HAVING total_orders 10 OR dept_id IS NULL ORDER BY paid_amount DESC;这里有个细节值得琢磨HAVING在WITH ROLLUP之后执行总计行的total_orders是全部订单数如果总数不超过10本例不太可能但逻辑上是可能的会被HAVING total_orders 10过滤掉。所以要把dept_id IS NULL这个条件用OR放进HAVING保住合计行。但用IS NULL判断合计行有一个隐患如果某个真实的dept_id本身就有NULL值就分不清是汇总行还是真实业务数据。MySQL 8.0.1之后提供了GROUPING()函数来精确识别汇总行SELECT IF(GROUPING(dept_id), 全公司合计, dept_id) AS dept_label, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY dept_id WITH ROLLUP HAVING total_orders 10 OR GROUPING(dept_id) 1 ORDER BY paid_amount DESC;GROUPING(dept_id)返回1表示这一行是dept_id的汇总行这样即使业务数据里有NULL也不会误判。顺便说一嘴性能WITH ROLLUP是在分组结果上做追加计算代价远小于再跑一条聚合SQL然后UNION ALL是推荐做法。5.3 UNION ALL合并部门维度和月份维度的最终方案部门维度的报表搞定了现在还要一个月度维度的统计按月份展示订单总数、已支付总金额、最大单笔金额。然后要把部门和月份两个维度合并成一张结果集。注意两个维度的列结构必须一致所以我在SELECT里加一个维度标识列SELECT by_dept AS dim_type, IF(GROUPING(dept_id), 全公司合计, CAST(dept_id AS CHAR)) AS dim_value, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY dept_id WITH ROLLUP HAVING total_orders 10 OR GROUPING(dept_id) 1 UNION ALL SELECT by_month AS dim_type, DATE_FORMAT(create_time, %Y-%m) AS dim_value, COUNT(*) AS total_orders, SUM(CASE WHEN pay_status 1 THEN order_amount ELSE 0 END) AS paid_amount, MAX(order_amount) AS max_amount FROM order_record GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY dim_value;几个细节说明一下两个SELECT的列数完全一致5列所以UNION ALL可以正常拼接。第二个查询的GROUP BY直接用了表达式DATE_FORMAT(create_time, %Y-%m)这是一种非常常见的写法不需要额外包一层子查询。两边的dim_value类型要兼容CAST(dept_id AS CHAR)是为了和DATE_FORMAT产生的字符串对齐避免类型混乱。第二条SELECT的多余ORDER BY dim_value会不会被忽略回顾4.2节的坑没有括号包裹的单个SELECT里的ORDER BY在UNION里会被丢弃。所以如果我要对合并后的整体结果排序应该把ORDER BY放到整个语句末尾。上面这条SQL里末尾的ORDER BY dim_value的作用范围是整个UNION ALL的结果集这才会生效。跑完这条SQL你会得到两类行一类是dim_type为by_dept的部门维度数据含全公司合计另一类是by_month的月度数据。前端拿到这个结果集按照dim_type分组渲染就能在一个表格里同时展示两个维度的统计了。最后说点实际体会这三块知识点拆开看都不难但组合起来才是真正的考验。我自己在实际项目中踩过最大的坑反而是那些看起来最简单的地方COUNT(列名)漏掉了NULL、SUM没做COALESCE兜底、受group_concat_max_len影响的静默截断、GROUP BY隐式排序在8.0被移除导致报表顺序错乱。这些问题报错还好可怕的是它不报错只是结果不对排查起来极其费劲。我现在的习惯是写完聚合分组相关的SQL先跑一遍验证NULL的处理再用EXPLAIN看一眼有没有走临时表和filesort最后尽量用小数据集把HAVING、ROLLUP、UNION的边界条件都测一遍。另外在MySQL 8.0上做迁移或者新写代码GROUP BY之后一定显式写ORDER BY不要再依赖老版本的隐式排序。这套东西练熟了日常报表统计、数据分析类的需求基本都能覆盖。如果还想深入可以再去研究窗口函数和优化器对分组查询的执行计划那是另一篇能写很长的话题了。