做了很多年 MySQL 的数据查询我越来越觉得写聚合、分组、联合这三类查询本质上不是背语法而是在做一次空间搭建。真正的工作场景里它们很少单独出现更多是同一条 SQL 里互相嵌套、彼此拼接像搭立体的拼图——聚合负责变分组负责分联合负责并。这篇文章我想把这三块拼图各自的功能拆开讲透再重点说说它们组合拼装时最容易踩的坑适合刚接触 MySQL 的初级开发也适合已经会写基础查询、但一遇到组合需求就翻车的朋友。写 SQL 的人通常有个错觉只要把单个知识点背熟组合起来自然会。但现实是聚合函数和 GROUP BY、UNION 放在同一条语句里的时候会出现很多单看都对、合起来错的问题。我一直觉得如果能理解聚合是怎么把行数压扁的、分组是怎么划分边界的、联合是怎么纵向延长结果集的你就拥有了拼图的完整视角。下文我会用尽量通俗的比喻和能直接跑起来的例子来讲也会告诉你哪些地方是我实际项目里真吃过亏的。1. 聚合是第一块拼图COUNT、SUM、AVG 到底在坍缩什么1.1 聚合的直观本质把一组行压缩成一个结论聚合函数的最大特点是输入很多行、输出一行。就像一支团队的年底总结每个人是单独的一行记录但 SUM、AVG、COUNT 这些函数会把整组人的数据压成一个总数、一个平均值、一个人数。在没有 GROUP BY 的情况下聚合函数作用于整个结果集。比如SELECT COUNT(*) AS total_orders, SUM(amount) AS total_amount FROM sales_order WHERE status paid;这条语句把满足条件的全部订单行坍缩成一行统计结果。你原来在脑子里想象的每一行输出一个结果到这里必须切换为整个结果集输出一个结果。这是聚合思维和普通查询思维最大的分水岭理解不了这一点后面所有组合写法都会别扭。1.2 COUNT 系列星号、字段名、DISTINCT 的三种差异COUNT 是最容易写错但最不容易被发现的函数。很多人以为 COUNT(*) 和 COUNT(字段) 只是写法不同实际差别很大COUNT(*)统计行数完全不管这一行里有没有 NULL。COUNT(column)统计该列非 NULL 的行数只要某行这个字段是 NULL就不算进去。COUNT(DISTINCT column)对该列的非 NULL 值去重后计数。举个例子一张订单表里有 1000 行记录其中首单优惠码first_order_code有 300 行为 NULL顾客 IDcustomer_id的去重值为 200 个SELECT COUNT(*) AS total_rows, COUNT(first_order_code) AS has_code_rows, COUNT(DISTINCT customer_id) AS unique_customers FROM sales_order;跑出来的结果很可能是 1000、700、200。如果你本意是有多少个顾客下了单写成COUNT(customer_id)就会得到 1000 这种毫无意义的答案——正确写法必须是COUNT(DISTINCT customer_id)。提示需要去重计数时直接在 SQL 里写COUNT(DISTINCT ...)不要先把全量数据拉回程序里再数那样既浪费传输量又容易在代码里引入 bug。1.3 SUM 与 AVGNULL 的处理比想象中更隐蔽SUM 和 AVG 都会忽略 NULL 行但它们遇到整组都是 NULL时的表现不同。SUM(amount)如果这一组所有行的 amount 都是 NULL结果是 NULL 而不是 0。这在报表里非常坑所以实际项目中我几乎总是写SELECT COALESCE(SUM(amount), 0) AS total_amount FROM sales_order;AVG 有一个更隐蔽的地方它的分母只统计非 NULL 的行数。假设某员工三天的业绩分别是 100、NULL、200AVG(performance)算出来是 150而不是 100——因为 NULL 那天的数据在 AVG 看来不存在并没有参与分母。如果你本意是三天平均所以应该除以 3这个结果就会误导你。MAX 和 MIN 也不止能处理数字。日期、字符串同样可以比较大小所以MAX(pay_time)可以直接得到最近一次支付时间MIN(created_at)可以得到最早创建时间。这个用法在查首单时间、最近活跃时间时很顺手。1.4 GROUP_CONCATMySQL 特有的字符串聚合拼图块除了数值聚合MySQL 还提供了GROUP_CONCAT它能把组内的多个值拼成一个字符串。比如想知道每个品类下卖过哪些商品名SELECT category, GROUP_CONCAT(product_name ORDER BY quantity DESC SEPARATOR 、) AS product_list FROM sales_item GROUP BY category;这里有个我第一次用就踩中的坑GROUP_CONCAT有长度限制默认值group_concat_max_len是 1024 字节。商品名稍微一多拼出来的结果会被静默截断看起来就像答案少了一块。遇到这种情况可以在查询前执行SET SESSION group_concat_max_len 10240;只影响当前会话不改全局配置适合临时查看大字符串聚合结果。2. GROUP BY 决定答案的粒度分组字段选错问题就变味了2.1 分组是先分堆再聚合执行顺序有严格纪律GROUP BY 的执行时机在 WHERE 之后、HAVING 之前。标准逻辑顺序是这样的FROM - WHERE - GROUP BY - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT我把这个顺序背得很熟因为它能解释后面一半的坑。用生活里的话说WHERE 是进厨房之前的挑食材GROUP BY 是把挑好的食材分碗装聚合函数是数每碗有几颗HAVING 是端上桌前检查每碗合不合格。如果你在挑食材阶段就想数数会发现食材还没分碗根本没法按碗统计。看一个最基础的例子SELECT category, SUM(quantity) AS total_quantity, COUNT(*) AS item_lines FROM sales_item GROUP BY category;先按 category 归堆再对每个堆做 SUM、COUNT最终输出的是每个分类一行统计结果。理解了这个归堆动作你就理解了聚合为什么是在分组之后才发生的。2.2 分组粒度决定了答案的粗细先想好要回答哪个层级的问题粒度这个词听起来抽象但它是分组查询的灵魂。GROUP BY category回答的是品类层级的问题GROUP BY category, brand回答的是品类品牌层级的问题。分组字段越多输出行数越多粒度就越细。我的习惯是动手写 SQL 之前先用一句话说清楚要回答的问题。比如我想看每个品类下各品牌的销量这句话已经明确了分组字段就是category, brandSELECT category, brand, SUM(quantity) AS total_quantity FROM sales_item GROUP BY category, brand;粒度模糊是报表口径混乱的头号来源。同一个数字你按品类维度统计是 A 值按品牌维度统计是 B 值两者都有自己的意义但绝不能混着用。另外一个细节当分组列包含 NULL 时MySQL 会把所有 NULL 分到同一组里。这不是 bug是标准行为但如果 NULL 在你的表里代表未填写记得要么用COALESCE(category, 未分类)显式归拢要么提前在 WHERE 里排除。2.3 ONLY_FULL_GROUP_BYMySQL 为什么拒绝你顺手选的字段从 MySQL 5.7.5 开始ONLY_FULL_GROUP_BY默认开启。它的规则可以一句话概括SELECT 列表里出现的每一个非聚合字段都必须完整出现在 GROUP BY 里否则直接报错。-- 错误写法 SELECT category, unit_price, SUM(quantity) FROM sales_item GROUP BY category;这条语句报错的原因很朴素同一个 category 分组里unit_price 可能有多个不同的值数据库到底该显示哪一个标准 SQL 不接受哪一行都行这种随机答案干脆拒绝执行。正确的修法是如果想让 unit_price 参与统计就把它也加入 GROUP BY如果只是想顺便看一眼可以用ANY_VALUE(unit_price)明确告诉 MySQL我要这个分组里的任意值。这个方法在维护老项目、遇到无法改 SELECT 结构的场景时很实用但日常新写代码不建议过度使用因为它本质上是在绕开分组的严谨性。2.4 HAVING 是对组的筛选它和 WHERE 分工完全不同WHERE 在分组前过滤行HAVING 在分组后过滤组。这个分工直接决定了书写位置和语义。比如要找出销量大于 100 的品类SELECT category, SUM(quantity) AS total_quantity FROM sales_item GROUP BY category HAVING SUM(quantity) 100;HAVING里可以写聚合函数因为此时分组已经完成。反过来如果试图在 WHERE 里写SUM(quantity) 100MySQL 会直接报Invalid use of group function——WHERE 执行时聚合结果根本还没算出来自然无法判断。更常见的误区是想过滤已支付的订单却把 status 写进 HAVING。status 根本不在分组字段里一个客户的分组内有多条订单status 就可能有多个值这种写法在 ONLY_FULL_GROUP_BY 开启时会被拒绝就算某些宽松模式放行了得到的也是毫无意义的组内任意值。正确做法永远是把行级过滤条件放到 WHERE 里。2.5 按时间分组时的索引问题函数包裹字段需谨慎按时间分组是报表里最频繁的操作常见写法是SELECT DATE_FORMAT(created_at, %Y-%m) AS month, SUM(amount) AS total_amount FROM sales_order GROUP BY DATE_FORMAT(created_at, %Y-%m);功能上没问题但DATE_FORMAT(created_at, ...)对字段套了函数MySQL 通常就难以直接使用 created_at 上的索引具体执行计划要以 EXPLAIN 为准。数据量大的时候这个查询会扫得很吃力。我的处理方案有两种一是表里额外加一个created_date DATE冗余列写入时直接存日期再为它建索引二是用生成列维护格式化后的月份字段。这样 GROUP BY 可以直接落在索引字段上。另一个细节DATE_FORMAT返回的是字符串月份排序按字典序来2025-02 会排在 2025-01 后面还好但跨年份时顺序就可能出问题。稳妥做法是 SELECT 里同时保留MAX(created_at)外层 ORDER BY 用真实时间。3. UNION 不是 JOIN 的亲戚纵向拼接的列数、类型与去重逻辑3.1 什么时候需要 UNION同构数据但分散在不同的桶里UNION 解决的是把多张结构相同的表或者多个同构的结果集纵向堆叠成一张大结果集的问题。常见场景包括历史分表、线下和线上渠道分开存的两套订单表、按年度拆分的流水表。这里必须先和 JOIN 分清JOIN 是横向拼接把两张表的列左右并在一起类比是两个人并排站合影UNION 是纵向拼接把两张表的行上下叠在一起类比是一群人排队集合队伍人数变多但每个人的字段宽度不变。搞混这两者是查询思路混乱的开始。看一个最简单例子一张线上订单表、一张线下订单表结构完全相同想拿到全渠道订单明细SELECT order_no, amount, online AS channel FROM order_online UNION ALL SELECT order_no, amount, offline FROM order_offline;注意第一段 SELECT 里的online是常量列作用是为结果集打一个渠道标记这在后续按渠道聚合时非常有用。3.2 列数一致、类型兼容、列名取自第一段 SELECTUNION 的规则特别像拼积木两边接口必须对得上。两个 SELECT 的列数必须完全一致对应列的数据类型要兼容——MySQL 允许隐式转换但把数字列拼到字符串列上会引发意外的转换结果。另外最终结果集的列名由第一段 SELECT 的别名决定第二段的别名会被忽略。所以写 UNION 时第一段 SELECT 的别名要认真命名它就是整张结果集对外暴露的字段名。3.3 UNION 与 UNION ALL默认去重的成本比你想象的高这里是我最想强调的一点UNION等价于UNION DISTINCT它会对所有列做一次去重比较数据量大时需要排序甚至动用临时表空间。而UNION ALL只是简单地把行堆在一起不做去重判断。大多数业务场景其实是把分散的数据拼起来继续算根本不需要去重。这时候用默认的 UNION 等于让数据库多做一轮无意义的工作。我的习惯是纯拼接一律UNION ALL真的存在两份数据中可能有完全相同的行这种去重需求时再放到最外层用DISTINCT或GROUP BY显式处理。这样既明确又可控。还有一个隐蔽点UNION 去重时会把 NULL 视为同一个值。如果某行关键列全是 NULL可能被合并成一行丢失你本不想丢失的记录。3.4 各段内部排序、限量括号的语法细节别记错默认情况下ORDER BY 只能出现在整个 UNION 的最外层。如果某一段内部想先排序并 LIMITMySQL 里必须把这一段用括号包起来(SELECT order_no, amount FROM order_online ORDER BY amount DESC LIMIT 5) UNION ALL (SELECT order_no, amount FROM order_offline ORDER BY amount DESC LIMIT 5) ORDER BY amount DESC;第一段 SELECT 后面如果不加括号直接写 ORDER BYMySQL 会直接报语法错误。我见过很多同事在这里被卡住其实记住括号可保平安就够了。整体排序放在 UNION 之后这时 ORDER BY 可以引用第一段 SELECT 定义的别名也可以写列序号但我强烈建议写别名——列序号在后续有人调整 SELECT 列表时指向的列会悄悄变化是隐形的维护炸弹。3.5 UNION 之后再聚合把 UNION 结果当成一张临时表组合拼图的关键动作是把 UNION 的结果包成一个派生表在外层继续 GROUP BY。比如按渠道统计各渠道的订单数和总额SELECT channel, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM ( SELECT order_no, amount, online AS channel FROM order_online UNION ALL SELECT order_no, amount, offline FROM order_offline ) t GROUP BY channel;这里的t是派生表的别名MySQL 强制要求必须写不写直接报错。这个包一层的思想是整个聚合、分组、联合组合使用的核心桥梁——UNION 负责把数据汇到一起GROUP BY 负责在汇合后的总结果集上重新划分边界聚合函数负责在每个边界内收拢结论。4. 三块拼图同时出场时最容易翻车的四个接缝点4.1 先 JOIN 再 GROUP BY重复计数是报表里最经典的翻车点这个坑我踩过不止一次。订单主表和订单明细表是一对多关系一条订单对应多条明细。当你把两张表 JOIN 起来再按品类分组订单主表里的金额字段会被明细行放大。-- 错误示例SUM(o.amount) 会把多明细订单的总金额重复累加 SELECT i.category, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.amount) AS total_amount FROM sales_order o JOIN sales_item i ON i.order_id o.order_id WHERE o.status paid GROUP BY i.category;这里COUNT(DISTINCT o.order_id)因为去重了订单数是对的但SUM(o.amount)完全错了——一个订单只要有三条明细它的金额就会被累加三次。总额虚高的幅度取决于订单平均明细数。正确思路是先明确口径。如果统计的是品类级的销售额应该用明细行小计price * quantity来聚合因为每一行明细是唯一的SELECT i.category, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(i.quantity * i.unit_price) AS category_amount FROM sales_order o JOIN sales_item i ON i.order_id o.order_id WHERE o.status paid GROUP BY i.category;如果坚持要按主表金额统计那就必须在 JOIN 之前先把订单表聚合到订单维度再和明细关联。这种先降维再关联的思路能避开绝大多数重复计数问题。4.2 WHERE 与 HAVING 的时序混淆行级过滤和组级过滤不是一回事执行顺序我前面已经强调过WHERE 在分组前HAVING 在分组后。落到实际编码中最常见的错误是想过滤行却写进了 HAVING。-- 错误示例status 不是分组字段这个过滤应该发生在分组前 SELECT customer_id, SUM(amount) AS total_amount FROM sales_order GROUP BY customer_id HAVING status paid;一个顾客分组内可能同时存在已支付和未支付的订单status 是组内多值字段拿它做过滤在语义上就是错的。在 ONLY_FULL_GROUP_BY 开启时MySQL 会直接拒绝就算旧库关了该模式侥幸通过结果也是随机挑选一行的 status 来判断根本不可信。正确写法是把 status 条件放到 WHERESELECT customer_id, SUM(amount) AS total_amount FROM sales_order WHERE status paid GROUP BY customer_id;区分两者的口诀我一直在用WHERE 是挑哪些行参与统计HAVING 是挑哪些组留在结果里。4.3 聚合函数不能直接嵌套想要均值的均值得先包一层子查询有时候需求是算每个品类的平均价格再求所有品类的平均价格的均值。新手很自然会写AVG(AVG(price))但 MySQL 会直接报Invalid use of group function——聚合函数不能直接嵌套聚合函数。解法还是那句老话包一层。先在内层算出每个品类的均值再在外层对这些均值求平均SELECT AVG(category_avg_price) AS overall_avg FROM ( SELECT category, AVG(unit_price) AS category_avg_price FROM sales_item GROUP BY category ) t;内层结果集对 SQL 引擎而言就是一张普通临时表外层可以对它继续做任何聚合。这个思路和 UNION 后再聚合是同一个套路把中间结果显式表达成子查询复杂度就瞬间清晰了。4.4 UNION 组合时的排序、限制和 NULL 合并陷阱把 UNION 和 ORDER BY、LIMIT 组合时最常出现的问题是想要每个分段各自 Top N结果却不是自己预期的样子。(SELECT category, SUM(quantity) AS qty, 上半年 AS period FROM sales_item WHERE created_at 2025-07-01 GROUP BY category ORDER BY qty DESC LIMIT 3) UNION ALL (SELECT category, SUM(quantity), 下半年 FROM sales_item WHERE created_at 2025-07-01 GROUP BY category ORDER BY qty DESC LIMIT 3) ORDER BY period, qty DESC;这个写法依赖括号实现每段先各自排序取前三再整体按 period 排。如果不加括号第一段里的 ORDER BY 要么语法报错要么被忽略。另外注意UNION 的隐式去重如果同时存在会按照整行比较去合并包含 NULL 的行极有可能被吞掉。所以只要不是真的需要所有列相同才合并就用 UNION ALL 保住每一行。5. 实战推演从需求到 SQL 的完整构造过程纸上谈兵再多不如把一套完整需求从头拆到尾。假设某项目同时运营线上商城和线下门店两张订单表结构完全一致另有一张订单明细表。运营提了一个组合需求分别统计本月线上、线下两个渠道的支付订单数和销售额再给出两个渠道各自销售额前三的品类最后要一份全渠道的品类销售排行。表结构简化为-- 线上订单表 order_onlineorder_id, amount, status, pay_time -- 线下订单表 order_offlineorder_id, amount, status, pay_time -- 订单明细表 order_itemid, order_id, category, quantity, unit_price第一步定口径。时间范围取pay_time 2025-06-01 AND pay_time 2025-07-01状态取status 1表示已支付。销售额采用明细行小计quantity * unit_price聚合避免主表金额在 JOIN 明细后重复累加。第二步算各渠道头部指标。因为订单明细表在两份订单表里分别关联先算线上再算线下最后 UNION ALL 拼起来SELECT online AS channel, COUNT(DISTINCT o.order_id) AS paid_orders, SUM(i.quantity * i.unit_price) AS channel_amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 UNION ALL SELECT offline, COUNT(DISTINCT o.order_id), SUM(i.quantity * i.unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01;第三步算各渠道销售额前三的品类。每个渠道单独查询、排序、限量再用括号包起来做 UNION ALL(SELECT online AS channel, category, SUM(quantity * unit_price) AS amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY category ORDER BY amount DESC LIMIT 3) UNION ALL (SELECT offline, category, SUM(quantity * unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY category ORDER BY amount DESC LIMIT 3) ORDER BY channel, amount DESC;注意第二段的别名其实不会生效最终排序用的是第一段的 channel 和 amount。第四步最关键的组合求全渠道品类销售排行。这里有两个策略我强烈推荐先各渠道聚合到品类再 UNION ALL最后外层再聚合SELECT category, SUM(amount) AS total_amount FROM ( SELECT i.category AS category, SUM(i.quantity * i.unit_price) AS amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY i.category UNION ALL SELECT i.category, SUM(i.quantity * i.unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY i.category ) t GROUP BY category ORDER BY total_amount DESC LIMIT 10;为什么要这样写因为 UNION 之前两个渠道分别只在品类维度上留了十多行聚合结果派生表 t 的数据量可能只有几十行如果直接 UNION ALL 明细行t 里装的是全月所有订单明细可能是几十万行。两者的性能天差地别。这种先聚合、后合并、再聚合的写法是在同一条 SQL 里同时用好聚合、分组、联合三块拼图的完整示范。6. 用顺手之后沉淀下来的几条经验写了这些年 SQL真正让我效率翻倍的往往不是更高级的函数而是几个朴素的习惯。第一动手前先问口径。按订单统计还是按明细统计金额用主表还是明细小计分组粒度到哪一级口径不清SQL 写多漂亮都是白搭。我自己见过太多因为口径不一致导致报表对不上账的案例最后排查下来根本不是 SQL 写错而是当初没定义清楚。第二遇到JOIN 之后直接 GROUP BY 聚合主表字段的写法先停下来检查重复计数。能用COUNT(DISTINCT)解决的用COUNT(DISTINCT)解决不了就拆子查询先在低粒度层算完再关联。第三写 GROUP BY 时SELECT 里的非聚合字段全部进 GROUP BY不依赖 MySQL 的宽松模式。版本升级、参数调整都可能让旧写法一夜之间报错养成严谨习惯能少很多麻烦。第四UNION 默认是去重的但大多数拼接场景根本不需要去重无脑用 UNION ALL 更稳更快。真的要去重放到外层用 GROUP BY 或 DISTINCT 显式表达意图一目了然。第五派生表的行数决定性能。数据量大的时候尽量把聚合下沉到 UNION 之前做让临时表小一点再小一点。最后说个我自己的教训。早年间做报表我用 UNION 把两个渠道的订单拼在一起发现总订单数对不上一直怀疑是 JOIN 的问题。排查了整整一个下午最后发现是 UNION 的隐式去重把两个渠道里恰好相同的字段组合合并成了一行。从那之后凡是纯拼接我一律写 UNION ALL这个习惯救了我无数次。希望这篇东西能帮你在组合聚合、分组、联合的时候少走几个这样的弯路。
做了很多年 MySQL 的数据查询我越来越觉得写聚合、分组、联合这三类查询本质上不是背语法而是在做一次空间搭建。真正的工作场景里它们很少单独出现更多是同一条 SQL 里互相嵌套、彼此拼接像搭立体的拼图——聚合负责变分组负责分联合负责并。这篇文章我想把这三块拼图各自的功能拆开讲透再重点说说它们组合拼装时最容易踩的坑适合刚接触 MySQL 的初级开发也适合已经会写基础查询、但一遇到组合需求就翻车的朋友。写 SQL 的人通常有个错觉只要把单个知识点背熟组合起来自然会。但现实是聚合函数和 GROUP BY、UNION 放在同一条语句里的时候会出现很多单看都对、合起来错的问题。我一直觉得如果能理解聚合是怎么把行数压扁的、分组是怎么划分边界的、联合是怎么纵向延长结果集的你就拥有了拼图的完整视角。下文我会用尽量通俗的比喻和能直接跑起来的例子来讲也会告诉你哪些地方是我实际项目里真吃过亏的。1. 聚合是第一块拼图COUNT、SUM、AVG 到底在坍缩什么1.1 聚合的直观本质把一组行压缩成一个结论聚合函数的最大特点是输入很多行、输出一行。就像一支团队的年底总结每个人是单独的一行记录但 SUM、AVG、COUNT 这些函数会把整组人的数据压成一个总数、一个平均值、一个人数。在没有 GROUP BY 的情况下聚合函数作用于整个结果集。比如SELECT COUNT(*) AS total_orders, SUM(amount) AS total_amount FROM sales_order WHERE status paid;这条语句把满足条件的全部订单行坍缩成一行统计结果。你原来在脑子里想象的每一行输出一个结果到这里必须切换为整个结果集输出一个结果。这是聚合思维和普通查询思维最大的分水岭理解不了这一点后面所有组合写法都会别扭。1.2 COUNT 系列星号、字段名、DISTINCT 的三种差异COUNT 是最容易写错但最不容易被发现的函数。很多人以为 COUNT(*) 和 COUNT(字段) 只是写法不同实际差别很大COUNT(*)统计行数完全不管这一行里有没有 NULL。COUNT(column)统计该列非 NULL 的行数只要某行这个字段是 NULL就不算进去。COUNT(DISTINCT column)对该列的非 NULL 值去重后计数。举个例子一张订单表里有 1000 行记录其中首单优惠码first_order_code有 300 行为 NULL顾客 IDcustomer_id的去重值为 200 个SELECT COUNT(*) AS total_rows, COUNT(first_order_code) AS has_code_rows, COUNT(DISTINCT customer_id) AS unique_customers FROM sales_order;跑出来的结果很可能是 1000、700、200。如果你本意是有多少个顾客下了单写成COUNT(customer_id)就会得到 1000 这种毫无意义的答案——正确写法必须是COUNT(DISTINCT customer_id)。提示需要去重计数时直接在 SQL 里写COUNT(DISTINCT ...)不要先把全量数据拉回程序里再数那样既浪费传输量又容易在代码里引入 bug。1.3 SUM 与 AVGNULL 的处理比想象中更隐蔽SUM 和 AVG 都会忽略 NULL 行但它们遇到整组都是 NULL时的表现不同。SUM(amount)如果这一组所有行的 amount 都是 NULL结果是 NULL 而不是 0。这在报表里非常坑所以实际项目中我几乎总是写SELECT COALESCE(SUM(amount), 0) AS total_amount FROM sales_order;AVG 有一个更隐蔽的地方它的分母只统计非 NULL 的行数。假设某员工三天的业绩分别是 100、NULL、200AVG(performance)算出来是 150而不是 100——因为 NULL 那天的数据在 AVG 看来不存在并没有参与分母。如果你本意是三天平均所以应该除以 3这个结果就会误导你。MAX 和 MIN 也不止能处理数字。日期、字符串同样可以比较大小所以MAX(pay_time)可以直接得到最近一次支付时间MIN(created_at)可以得到最早创建时间。这个用法在查首单时间、最近活跃时间时很顺手。1.4 GROUP_CONCATMySQL 特有的字符串聚合拼图块除了数值聚合MySQL 还提供了GROUP_CONCAT它能把组内的多个值拼成一个字符串。比如想知道每个品类下卖过哪些商品名SELECT category, GROUP_CONCAT(product_name ORDER BY quantity DESC SEPARATOR 、) AS product_list FROM sales_item GROUP BY category;这里有个我第一次用就踩中的坑GROUP_CONCAT有长度限制默认值group_concat_max_len是 1024 字节。商品名稍微一多拼出来的结果会被静默截断看起来就像答案少了一块。遇到这种情况可以在查询前执行SET SESSION group_concat_max_len 10240;只影响当前会话不改全局配置适合临时查看大字符串聚合结果。2. GROUP BY 决定答案的粒度分组字段选错问题就变味了2.1 分组是先分堆再聚合执行顺序有严格纪律GROUP BY 的执行时机在 WHERE 之后、HAVING 之前。标准逻辑顺序是这样的FROM - WHERE - GROUP BY - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT我把这个顺序背得很熟因为它能解释后面一半的坑。用生活里的话说WHERE 是进厨房之前的挑食材GROUP BY 是把挑好的食材分碗装聚合函数是数每碗有几颗HAVING 是端上桌前检查每碗合不合格。如果你在挑食材阶段就想数数会发现食材还没分碗根本没法按碗统计。看一个最基础的例子SELECT category, SUM(quantity) AS total_quantity, COUNT(*) AS item_lines FROM sales_item GROUP BY category;先按 category 归堆再对每个堆做 SUM、COUNT最终输出的是每个分类一行统计结果。理解了这个归堆动作你就理解了聚合为什么是在分组之后才发生的。2.2 分组粒度决定了答案的粗细先想好要回答哪个层级的问题粒度这个词听起来抽象但它是分组查询的灵魂。GROUP BY category回答的是品类层级的问题GROUP BY category, brand回答的是品类品牌层级的问题。分组字段越多输出行数越多粒度就越细。我的习惯是动手写 SQL 之前先用一句话说清楚要回答的问题。比如我想看每个品类下各品牌的销量这句话已经明确了分组字段就是category, brandSELECT category, brand, SUM(quantity) AS total_quantity FROM sales_item GROUP BY category, brand;粒度模糊是报表口径混乱的头号来源。同一个数字你按品类维度统计是 A 值按品牌维度统计是 B 值两者都有自己的意义但绝不能混着用。另外一个细节当分组列包含 NULL 时MySQL 会把所有 NULL 分到同一组里。这不是 bug是标准行为但如果 NULL 在你的表里代表未填写记得要么用COALESCE(category, 未分类)显式归拢要么提前在 WHERE 里排除。2.3 ONLY_FULL_GROUP_BYMySQL 为什么拒绝你顺手选的字段从 MySQL 5.7.5 开始ONLY_FULL_GROUP_BY默认开启。它的规则可以一句话概括SELECT 列表里出现的每一个非聚合字段都必须完整出现在 GROUP BY 里否则直接报错。-- 错误写法 SELECT category, unit_price, SUM(quantity) FROM sales_item GROUP BY category;这条语句报错的原因很朴素同一个 category 分组里unit_price 可能有多个不同的值数据库到底该显示哪一个标准 SQL 不接受哪一行都行这种随机答案干脆拒绝执行。正确的修法是如果想让 unit_price 参与统计就把它也加入 GROUP BY如果只是想顺便看一眼可以用ANY_VALUE(unit_price)明确告诉 MySQL我要这个分组里的任意值。这个方法在维护老项目、遇到无法改 SELECT 结构的场景时很实用但日常新写代码不建议过度使用因为它本质上是在绕开分组的严谨性。2.4 HAVING 是对组的筛选它和 WHERE 分工完全不同WHERE 在分组前过滤行HAVING 在分组后过滤组。这个分工直接决定了书写位置和语义。比如要找出销量大于 100 的品类SELECT category, SUM(quantity) AS total_quantity FROM sales_item GROUP BY category HAVING SUM(quantity) 100;HAVING里可以写聚合函数因为此时分组已经完成。反过来如果试图在 WHERE 里写SUM(quantity) 100MySQL 会直接报Invalid use of group function——WHERE 执行时聚合结果根本还没算出来自然无法判断。更常见的误区是想过滤已支付的订单却把 status 写进 HAVING。status 根本不在分组字段里一个客户的分组内有多条订单status 就可能有多个值这种写法在 ONLY_FULL_GROUP_BY 开启时会被拒绝就算某些宽松模式放行了得到的也是毫无意义的组内任意值。正确做法永远是把行级过滤条件放到 WHERE 里。2.5 按时间分组时的索引问题函数包裹字段需谨慎按时间分组是报表里最频繁的操作常见写法是SELECT DATE_FORMAT(created_at, %Y-%m) AS month, SUM(amount) AS total_amount FROM sales_order GROUP BY DATE_FORMAT(created_at, %Y-%m);功能上没问题但DATE_FORMAT(created_at, ...)对字段套了函数MySQL 通常就难以直接使用 created_at 上的索引具体执行计划要以 EXPLAIN 为准。数据量大的时候这个查询会扫得很吃力。我的处理方案有两种一是表里额外加一个created_date DATE冗余列写入时直接存日期再为它建索引二是用生成列维护格式化后的月份字段。这样 GROUP BY 可以直接落在索引字段上。另一个细节DATE_FORMAT返回的是字符串月份排序按字典序来2025-02 会排在 2025-01 后面还好但跨年份时顺序就可能出问题。稳妥做法是 SELECT 里同时保留MAX(created_at)外层 ORDER BY 用真实时间。3. UNION 不是 JOIN 的亲戚纵向拼接的列数、类型与去重逻辑3.1 什么时候需要 UNION同构数据但分散在不同的桶里UNION 解决的是把多张结构相同的表或者多个同构的结果集纵向堆叠成一张大结果集的问题。常见场景包括历史分表、线下和线上渠道分开存的两套订单表、按年度拆分的流水表。这里必须先和 JOIN 分清JOIN 是横向拼接把两张表的列左右并在一起类比是两个人并排站合影UNION 是纵向拼接把两张表的行上下叠在一起类比是一群人排队集合队伍人数变多但每个人的字段宽度不变。搞混这两者是查询思路混乱的开始。看一个最简单例子一张线上订单表、一张线下订单表结构完全相同想拿到全渠道订单明细SELECT order_no, amount, online AS channel FROM order_online UNION ALL SELECT order_no, amount, offline FROM order_offline;注意第一段 SELECT 里的online是常量列作用是为结果集打一个渠道标记这在后续按渠道聚合时非常有用。3.2 列数一致、类型兼容、列名取自第一段 SELECTUNION 的规则特别像拼积木两边接口必须对得上。两个 SELECT 的列数必须完全一致对应列的数据类型要兼容——MySQL 允许隐式转换但把数字列拼到字符串列上会引发意外的转换结果。另外最终结果集的列名由第一段 SELECT 的别名决定第二段的别名会被忽略。所以写 UNION 时第一段 SELECT 的别名要认真命名它就是整张结果集对外暴露的字段名。3.3 UNION 与 UNION ALL默认去重的成本比你想象的高这里是我最想强调的一点UNION等价于UNION DISTINCT它会对所有列做一次去重比较数据量大时需要排序甚至动用临时表空间。而UNION ALL只是简单地把行堆在一起不做去重判断。大多数业务场景其实是把分散的数据拼起来继续算根本不需要去重。这时候用默认的 UNION 等于让数据库多做一轮无意义的工作。我的习惯是纯拼接一律UNION ALL真的存在两份数据中可能有完全相同的行这种去重需求时再放到最外层用DISTINCT或GROUP BY显式处理。这样既明确又可控。还有一个隐蔽点UNION 去重时会把 NULL 视为同一个值。如果某行关键列全是 NULL可能被合并成一行丢失你本不想丢失的记录。3.4 各段内部排序、限量括号的语法细节别记错默认情况下ORDER BY 只能出现在整个 UNION 的最外层。如果某一段内部想先排序并 LIMITMySQL 里必须把这一段用括号包起来(SELECT order_no, amount FROM order_online ORDER BY amount DESC LIMIT 5) UNION ALL (SELECT order_no, amount FROM order_offline ORDER BY amount DESC LIMIT 5) ORDER BY amount DESC;第一段 SELECT 后面如果不加括号直接写 ORDER BYMySQL 会直接报语法错误。我见过很多同事在这里被卡住其实记住括号可保平安就够了。整体排序放在 UNION 之后这时 ORDER BY 可以引用第一段 SELECT 定义的别名也可以写列序号但我强烈建议写别名——列序号在后续有人调整 SELECT 列表时指向的列会悄悄变化是隐形的维护炸弹。3.5 UNION 之后再聚合把 UNION 结果当成一张临时表组合拼图的关键动作是把 UNION 的结果包成一个派生表在外层继续 GROUP BY。比如按渠道统计各渠道的订单数和总额SELECT channel, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM ( SELECT order_no, amount, online AS channel FROM order_online UNION ALL SELECT order_no, amount, offline FROM order_offline ) t GROUP BY channel;这里的t是派生表的别名MySQL 强制要求必须写不写直接报错。这个包一层的思想是整个聚合、分组、联合组合使用的核心桥梁——UNION 负责把数据汇到一起GROUP BY 负责在汇合后的总结果集上重新划分边界聚合函数负责在每个边界内收拢结论。4. 三块拼图同时出场时最容易翻车的四个接缝点4.1 先 JOIN 再 GROUP BY重复计数是报表里最经典的翻车点这个坑我踩过不止一次。订单主表和订单明细表是一对多关系一条订单对应多条明细。当你把两张表 JOIN 起来再按品类分组订单主表里的金额字段会被明细行放大。-- 错误示例SUM(o.amount) 会把多明细订单的总金额重复累加 SELECT i.category, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(o.amount) AS total_amount FROM sales_order o JOIN sales_item i ON i.order_id o.order_id WHERE o.status paid GROUP BY i.category;这里COUNT(DISTINCT o.order_id)因为去重了订单数是对的但SUM(o.amount)完全错了——一个订单只要有三条明细它的金额就会被累加三次。总额虚高的幅度取决于订单平均明细数。正确思路是先明确口径。如果统计的是品类级的销售额应该用明细行小计price * quantity来聚合因为每一行明细是唯一的SELECT i.category, COUNT(DISTINCT o.order_id) AS order_cnt, SUM(i.quantity * i.unit_price) AS category_amount FROM sales_order o JOIN sales_item i ON i.order_id o.order_id WHERE o.status paid GROUP BY i.category;如果坚持要按主表金额统计那就必须在 JOIN 之前先把订单表聚合到订单维度再和明细关联。这种先降维再关联的思路能避开绝大多数重复计数问题。4.2 WHERE 与 HAVING 的时序混淆行级过滤和组级过滤不是一回事执行顺序我前面已经强调过WHERE 在分组前HAVING 在分组后。落到实际编码中最常见的错误是想过滤行却写进了 HAVING。-- 错误示例status 不是分组字段这个过滤应该发生在分组前 SELECT customer_id, SUM(amount) AS total_amount FROM sales_order GROUP BY customer_id HAVING status paid;一个顾客分组内可能同时存在已支付和未支付的订单status 是组内多值字段拿它做过滤在语义上就是错的。在 ONLY_FULL_GROUP_BY 开启时MySQL 会直接拒绝就算旧库关了该模式侥幸通过结果也是随机挑选一行的 status 来判断根本不可信。正确写法是把 status 条件放到 WHERESELECT customer_id, SUM(amount) AS total_amount FROM sales_order WHERE status paid GROUP BY customer_id;区分两者的口诀我一直在用WHERE 是挑哪些行参与统计HAVING 是挑哪些组留在结果里。4.3 聚合函数不能直接嵌套想要均值的均值得先包一层子查询有时候需求是算每个品类的平均价格再求所有品类的平均价格的均值。新手很自然会写AVG(AVG(price))但 MySQL 会直接报Invalid use of group function——聚合函数不能直接嵌套聚合函数。解法还是那句老话包一层。先在内层算出每个品类的均值再在外层对这些均值求平均SELECT AVG(category_avg_price) AS overall_avg FROM ( SELECT category, AVG(unit_price) AS category_avg_price FROM sales_item GROUP BY category ) t;内层结果集对 SQL 引擎而言就是一张普通临时表外层可以对它继续做任何聚合。这个思路和 UNION 后再聚合是同一个套路把中间结果显式表达成子查询复杂度就瞬间清晰了。4.4 UNION 组合时的排序、限制和 NULL 合并陷阱把 UNION 和 ORDER BY、LIMIT 组合时最常出现的问题是想要每个分段各自 Top N结果却不是自己预期的样子。(SELECT category, SUM(quantity) AS qty, 上半年 AS period FROM sales_item WHERE created_at 2025-07-01 GROUP BY category ORDER BY qty DESC LIMIT 3) UNION ALL (SELECT category, SUM(quantity), 下半年 FROM sales_item WHERE created_at 2025-07-01 GROUP BY category ORDER BY qty DESC LIMIT 3) ORDER BY period, qty DESC;这个写法依赖括号实现每段先各自排序取前三再整体按 period 排。如果不加括号第一段里的 ORDER BY 要么语法报错要么被忽略。另外注意UNION 的隐式去重如果同时存在会按照整行比较去合并包含 NULL 的行极有可能被吞掉。所以只要不是真的需要所有列相同才合并就用 UNION ALL 保住每一行。5. 实战推演从需求到 SQL 的完整构造过程纸上谈兵再多不如把一套完整需求从头拆到尾。假设某项目同时运营线上商城和线下门店两张订单表结构完全一致另有一张订单明细表。运营提了一个组合需求分别统计本月线上、线下两个渠道的支付订单数和销售额再给出两个渠道各自销售额前三的品类最后要一份全渠道的品类销售排行。表结构简化为-- 线上订单表 order_onlineorder_id, amount, status, pay_time -- 线下订单表 order_offlineorder_id, amount, status, pay_time -- 订单明细表 order_itemid, order_id, category, quantity, unit_price第一步定口径。时间范围取pay_time 2025-06-01 AND pay_time 2025-07-01状态取status 1表示已支付。销售额采用明细行小计quantity * unit_price聚合避免主表金额在 JOIN 明细后重复累加。第二步算各渠道头部指标。因为订单明细表在两份订单表里分别关联先算线上再算线下最后 UNION ALL 拼起来SELECT online AS channel, COUNT(DISTINCT o.order_id) AS paid_orders, SUM(i.quantity * i.unit_price) AS channel_amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 UNION ALL SELECT offline, COUNT(DISTINCT o.order_id), SUM(i.quantity * i.unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01;第三步算各渠道销售额前三的品类。每个渠道单独查询、排序、限量再用括号包起来做 UNION ALL(SELECT online AS channel, category, SUM(quantity * unit_price) AS amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY category ORDER BY amount DESC LIMIT 3) UNION ALL (SELECT offline, category, SUM(quantity * unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY category ORDER BY amount DESC LIMIT 3) ORDER BY channel, amount DESC;注意第二段的别名其实不会生效最终排序用的是第一段的 channel 和 amount。第四步最关键的组合求全渠道品类销售排行。这里有两个策略我强烈推荐先各渠道聚合到品类再 UNION ALL最后外层再聚合SELECT category, SUM(amount) AS total_amount FROM ( SELECT i.category AS category, SUM(i.quantity * i.unit_price) AS amount FROM order_online o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY i.category UNION ALL SELECT i.category, SUM(i.quantity * i.unit_price) FROM order_offline o JOIN order_item i ON i.order_id o.order_id WHERE o.status 1 AND o.pay_time 2025-06-01 AND o.pay_time 2025-07-01 GROUP BY i.category ) t GROUP BY category ORDER BY total_amount DESC LIMIT 10;为什么要这样写因为 UNION 之前两个渠道分别只在品类维度上留了十多行聚合结果派生表 t 的数据量可能只有几十行如果直接 UNION ALL 明细行t 里装的是全月所有订单明细可能是几十万行。两者的性能天差地别。这种先聚合、后合并、再聚合的写法是在同一条 SQL 里同时用好聚合、分组、联合三块拼图的完整示范。6. 用顺手之后沉淀下来的几条经验写了这些年 SQL真正让我效率翻倍的往往不是更高级的函数而是几个朴素的习惯。第一动手前先问口径。按订单统计还是按明细统计金额用主表还是明细小计分组粒度到哪一级口径不清SQL 写多漂亮都是白搭。我自己见过太多因为口径不一致导致报表对不上账的案例最后排查下来根本不是 SQL 写错而是当初没定义清楚。第二遇到JOIN 之后直接 GROUP BY 聚合主表字段的写法先停下来检查重复计数。能用COUNT(DISTINCT)解决的用COUNT(DISTINCT)解决不了就拆子查询先在低粒度层算完再关联。第三写 GROUP BY 时SELECT 里的非聚合字段全部进 GROUP BY不依赖 MySQL 的宽松模式。版本升级、参数调整都可能让旧写法一夜之间报错养成严谨习惯能少很多麻烦。第四UNION 默认是去重的但大多数拼接场景根本不需要去重无脑用 UNION ALL 更稳更快。真的要去重放到外层用 GROUP BY 或 DISTINCT 显式表达意图一目了然。第五派生表的行数决定性能。数据量大的时候尽量把聚合下沉到 UNION 之前做让临时表小一点再小一点。最后说个我自己的教训。早年间做报表我用 UNION 把两个渠道的订单拼在一起发现总订单数对不上一直怀疑是 JOIN 的问题。排查了整整一个下午最后发现是 UNION 的隐式去重把两个渠道里恰好相同的字段组合合并成了一行。从那之后凡是纯拼接我一律写 UNION ALL这个习惯救了我无数次。希望这篇东西能帮你在组合聚合、分组、联合的时候少走几个这样的弯路。
