如何通过SQL JOIN配合聚合函数计算每个分类的汇总指标？

JOIN后直接用GROUP BY会漏掉空分类，因INNER JOIN过滤无关联记录；应改用LEFT JOIN并以分类表为主表，用COUNT(orders.id)而非COUNT(*)，且时间等筛选条件须置于ON子句中避免NULL行被WHERE过滤。

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

很多人写 SELECT category, COUNT(*) FROM products JOIN orders ON products.id = orders.product_id GROUP BY category，结果发现某些分类压根不出现——因为 JOIN 默认是 INNER JOIN，没订单的分类直接被过滤掉了。要保留所有分类，必须用 LEFT JOIN，且聚合函数得放在主表（分类表）一侧。

正确写法是：先确保分类表做主表，再 LEFT JOIN 订单表；COUNT 要统计右表字段（比如 COUNT(orders.id)），而不是 COUNT(*)，否则空行也会算作 1。

• 主表必须是分类维度表（如 categories），不是事实表（如 orders）
• COUNT(orders.id) 统计关联到的订单数，COUNT(*) 会把左表每行都计 1
• 若需 SUM 或 AVG，同样要作用于右表字段，例如 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 处理，别让空值悄无声息拖垮指标。