MySQL多表左连接时分类、产品数量统计错误问题求解

你现有SQL的问题核心是两次左连接产生的行膨胀：当一个分类关联N个产品时，该分类对应的行在关联结果里会重复N次，因此直接count(c.cid)会统计到重复的分类ID，导致计数错误。可以用以下两种方案解决：

方案1：使用DISTINCT去重统计（写法简单，适合中小数据量） #

在计数时对唯一ID去重，即可避免重复行带来的计数错误：

SELECT 
  a.aid,
  COUNT(DISTINCT c.cid) AS category_count,
  COUNT(DISTINCT p.pid) AS product_count,
  IFNULL(MAX(p.ts), 0) AS max_sale_ts
FROM accounts a 
LEFT JOIN categories c ON c.aid = a.aid 
LEFT JOIN products p ON p.cid = c.cid 
GROUP BY a.aid;

说明：COUNT(DISTINCT)会自动忽略重复的ID值，IFNULL用于将无产品时MAX返回的NULL转为0，符合无数据返回0的要求。

方案2：预聚合后关联（性能更优，适合大数据量） #

先对分类下的产品做预聚合，再和上层表关联，从根源避免关联时的行膨胀：

SELECT 
  a.aid,
  COUNT(c.cid) AS category_count,
  IFNULL(SUM(p.cate_product_count), 0) AS product_count,
  IFNULL(MAX(p.cate_max_ts), 0) AS max_sale_ts
FROM accounts a
LEFT JOIN categories c ON c.aid = a.aid
LEFT JOIN (
  SELECT 
    cid,
    COUNT(pid) AS cate_product_count,
    MAX(ts) AS cate_max_ts
  FROM products
  GROUP BY cid
) p ON p.cid = c.cid
GROUP BY a.aid;

说明：子查询先按分类维度统计好每个分类的产品总数和最大销售时间戳，再和分类表关联时每个分类只会对应一行结果，不会产生冗余重复行，统计效率更高。

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