SQL JOIN与聚合函数计算各分类汇总指标的方法

JOIN 后直接用 GROUP BY 会漏掉空分类

先看一个常见错误写法：SELECT category, COUNT(*) FROM products JOIN orders ON products.id = orders.product_id GROUP BY category。这种情况下，没有订单的分类根本不会出现在结果里。正确做法是：确保分类表做主表，再LEFT JOIN订单表。COUNT要统计右表的字段，比如COUNT(orders.id)，而不是COUNT(*)——否则空行也会被算作1（因为左表每行都计了一次）。

• 主表必须是分类维度表（如categories），不是事实表（如orders）
• COUNT(orders.id)统计关联到的订单数，COUNT(*)会把左表每行都计1
• 若需SUM或A VG，同样要作用于右表字段，例如SUM(orders.amount)，空值自动忽略

WHERE 条件写在 JOIN ON 里还是外面？

另一个常见陷阱：想查“每个分类下2024年的订单总金额”，如果把时间条件写在WHERE orders.created_at >= '2024-01-01'，结果会导致没有2024年订单的分类彻底消失——因为WHERE会在JOIN之后过滤，把LEFT JOIN产生的NULL行也干掉了。正确的做法是把时间条件放进ON子句：LEFT JOIN orders ON products.category_id = orders.category_id AND orders.created_at >= '2024-01-01'。这样空分类仍然保留，聚合值显示为0或NULL。

• ON中的条件影响关联逻辑，WHERE中的条件影响最终结果集
• 多个筛选条件（如状态+时间）全得塞进ON，不能拆一半到WHERE
• 注意NULL处理：SUM返回NULL，可用COALESCE(SUM(...), 0)转为0

多层聚合（比如分类→子类→商品）容易重复计数

当分类表、子类表、商品表、订单表四层JOIN，再对订单金额求和时，很容易因笛卡尔积导致金额翻倍。比如一个商品属于某子类，该子类属于某分类，但JOIN后一行变多行，SUM就重复累加了。根本解法是分步聚合：先按商品聚合订单（SELECT product_id, SUM(amount) AS total_amount FROM orders GROUP BY product_id），再JOIN到商品→子类→分类链路。或者用子查询/CTE隔离聚合层级。

• 避免在宽JOIN后直接SUM多对一关系的明细字段
• 优先用子查询提前聚合，比在大结果集上GROUP BY更安全
• 用EXPLAIN检查执行计划，留意rows数是否异常膨胀

MySQL 8.0+ 和 PostgreSQL 对 NULL 分组行为不一致

数据库之间的差异也值得留意。MySQL在GROUP BY时默认允许SELECT *即使非分组字段没聚合，而PostgreSQL则会严格报错：column must appear in the GROUP BY clause or be used in an aggregate function。这不是语法错误，而是引擎设计差异。跨数据库可移植写法：所有SELECT列要么在GROUP BY中，要么套聚合函数。别依赖MySQL的宽松模式，否则迁移到PG或启用sql_mode=ONLY_FULL_GROUP_BY就崩了。

• PostgreSQL要求严格，MySQL默认宽松但建议主动开启ONLY_FULL_GROUP_BY
• GROUP BY category时，MAX(category_name)比裸写category_name更稳妥
• 不同数据库对NULL分组是否合并处理也有差异，测试时用含NULL的数据验证

实际写的时候，最常卡在JOIN类型选错和WHERE位置放错——这两处改对，八成问题就解决了。剩下就是检查聚合字段来源和NULL处理，别让空值悄无声息拖垮指标。