多对多JOIN后SUM/COUNT翻倍是因笛卡尔积导致主表字段复制，聚合在物理行而非逻辑行计算；正确解法是用子查询先按关联键GROUP BY预聚合，再LEFT JOIN回主表。
为什么多对多JOIN后SUM或COUNT会翻倍
因为SQL执行JOIN时按笛卡尔积展开：一个用户关联3个标签，就生成3行；主表字段被复制，聚合函数在物理行上计算，不是按业务逻辑行。你查“每个用户的订单总金额”，结果却是每笔明细加一遍，数值自然虚高——这不是bug，是关系代数的必然结果。
用子查询先GROUP BY再JOIN是最稳的解法
核心是切断行复制链：不让明细表直接参与最终分组，而是先压缩成每关联键一行，再拼回主表。这样主表每行只匹配1行聚合结果，SUM、COUNT、AVG全不会失真。
- 子查询必须按JOIN键（如
order_id、user_id）GROUP BY，否则无法对齐 - 别在子查询里漏掉
WHERE过滤条件（比如只统计status = 'paid'的订单），否则聚合基数错误 - MySQL 5.7+、PostgreSQL、SQL Server 都支持这种写法，执行计划通常能下推过滤，性能不输CTE
- 示例：算每个商品的入库总量和已售总量
SELECT g.goods_name, COALESCE(i.total_in, 0) AS total_in, COALESCE(o.total_out, 0) AS total_out, COALESCE(i.total_in, 0) - COALESCE(o.total_out, 0) AS stock_left FROM goods g LEFT JOIN ( SELECT goods_name, SUM(in_quantity) AS total_in FROM goods_in_stock GROUP BY goods_name ) i ON g.goods_name = i.goods_name LEFT JOIN ( SELECT goods_name, SUM(order_quantity) AS total_out FROM orders GROUP BY goods_name ) o ON g.goods_name = o.goods_name;
LEFT JOIN多对多时，COUNT(*)和COUNT(DISTINCT)的区别
直接COUNT(*)统计的是JOIN后的物理行数，比如1个商品有5条销售记录，就返回5；而COUNT(DISTINCT order_id)才是你真正想问的“这个商品卖出了几单”。但注意：COUNT(DISTINCT)只救计数，对SUM(amount)完全无效——它不会帮你把重复的amount去重相加，那会彻底破坏业务语义。
- 当右表存在NULL（LEFT JOIN未匹配），
COUNT(*)仍计1，COUNT(col)会忽略NULL，行为差异大 -
COUNT(DISTINCT)在大数据量下需哈希去重，比预聚合慢，且MySQL 5.7不支持COUNT(DISTINCT) OVER () - 如果右表有脏数据（比如同一
order_id存了两次），COUNT(DISTINCT)会掩盖问题，不如先查SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1
临时表 or CTE？选哪个更靠谱
两者逻辑等价，但落地时差别明显：临时表可加索引、可复用多次、支持CREATE INDEX优化后续JOIN；CTE只是语法封装，多数引擎（MySQL 8.0、PostgreSQL）每次引用都重算。如果你的子查询很重，又要在多个地方JOIN，优先建临时表并加索引——比如CREATE INDEX idx_orders_goods_name ON orders(goods_name)，能直接让JOIN从全表扫描变成索引查找。
- 临时表生命周期限于当前会话，不用手动
DROP，但要注意名字冲突 - CTE可读性好，适合简单预聚合；临时表更适合生产环境压测验证过的重逻辑
- 别用视图替代——视图不物化，嵌套太深时优化器容易放弃下推，反而更慢
实际翻倍往往不是单一原因。最常被忽略的是中间表缺失联合索引，导致JOIN走嵌套循环，放大膨胀效应；或者ON条件里混进了本该写在WHERE里的过滤，让LEFT JOIN悄悄退化成INNER JOIN。先跑EXPLAIN看rows_examined是否远超左表行数，再决定从哪一层切开查。
