MySQL中关联另一张表导致COUNT()统计数值膨胀问题排查

为什么会出现数值膨胀？ #

你碰到的这个问题，核心是多表左连接引发的重复行（笛卡尔积）。

原来的查询只关联了promo和sales表，是一对多的关系（一个促销员对应多条销售记录），这时候COUNT()统计的是真实的销售记录数，结果是准确的。但加入model表之后，如果sales里的某条记录对应的型号在model表中有多条匹配（比如同一个型号对应不同的分类条目），左连接后就会把这条销售记录复制多份——每匹配一条model记录，这条sales就会被多生成一行。

COUNT()会把这些重复的行全都算成有效记录，自然就导致统计数值莫名其妙变大了。比如假设某条销售记录在model表匹配了2条记录，那这条销售会被统计2次，最终Actual和SmallActual都会多算一次。

怎么修复这个问题？ #

解决思路就是先做子表聚合，再关联主表（也就是你查到的“表聚合”方法）：先把sales和model关联后的统计结果算出来，按促销员（和门店）分组得到真实的统计值，再把这个聚合好的结果和promo表关联，这样就不会出现连接时的行重复问题了。

给你两种靠谱的修复方案：

方案1：先聚合子表再关联（推荐）

这种方式从根源上避免了重复行，逻辑也更清晰：

SELECT 
  p.ID, 
  p.Branch, 
  p.Promoter,
  -- 用COALESCE处理没有销售记录的情况，避免出现NULL
  COALESCE(s_agg.Actual, 0) AS Actual,
  COALESCE(s_agg.SmallActual, 0) AS SmallActual
FROM sales.promo p
LEFT JOIN (
  -- 子查询先统计每个促销员+门店的真实销售数据
  SELECT 
    s.Promoter,
    s.Branch,
    -- 统计符合条件的总销售数
    COUNT(CASE WHEN s.brand = 'TEST' AND s.datesold BETWEEN '2023-01-01' AND '2023-12-31' THEN 1 END) AS Actual,
    -- 统计符合Small尺寸的销售数
    COUNT(CASE WHEN s.brand = 'TEST' AND s.datesold BETWEEN '2023-01-01' AND '2023-12-31' AND m.SizeRange = 'Small' THEN 1 END) AS SmallActual
  FROM sales.sales s
  LEFT JOIN sales.model m ON s.Model = m.Model
  GROUP BY s.Promoter, s.Branch
) s_agg ON p.Promoter = s_agg.Promoter AND p.Branch = s_agg.Branch
ORDER BY p.ID ASC;

方案2：对model表去重后再连接

如果你的model表中存在同一个型号对应多条记录的情况，可以先对model表去重，确保每个型号只返回一条匹配的记录：

SELECT 
  p.ID, 
  p.Branch, 
  p.Promoter,
  COUNT(CASE WHEN s.brand = 'TEST' AND s.datesold BETWEEN '2023-01-01' AND '2023-12-31' AND p.Branch = s.Branch THEN 1 END) AS Actual,
  COUNT(CASE WHEN s.brand = 'TEST' AND s.datesold BETWEEN '2023-01-01' AND '2023-12-31' AND p.Branch = s.Branch AND m.SizeRange = 'Small' THEN 1 END) AS SmallActual
FROM sales.promo p
LEFT JOIN sales.sales s ON p.Promoter = s.Promoter AND p.Branch = s.Branch
-- 先对model表去重，避免同一个型号返回多条记录
LEFT JOIN (
  SELECT DISTINCT Model, SizeRange 
  FROM sales.model
) m ON s.Model = m.Model
GROUP BY p.ID 
ORDER BY p.ID ASC;

表聚合的原理到底是什么？ #

简单来说，表聚合就是把需要统计的逻辑提前在子查询里完成，而不是先把所有表都连接起来再统计。

原来的写法是先把promo、sales、model三张表全连起来，这时候如果有一对多的关联，就会生成大量重复行；而表聚合是先在子查询里对sales和model的关联结果做分组统计，得到每个促销员+门店的真实统计值，此时每条统计结果都是唯一的，再和promo表关联时就不会出现重复行，统计数值自然就准确了。

这种方式不仅能解决数值膨胀的问题，还能提升查询效率——因为聚合后的子表数据量远小于全表连接的数据量，数据库处理起来更快。

内容的提问来源于stack exchange，提问作者Kasreti