COUNT子句返回错误计数值：多表JOIN后评论统计翻倍如何解决

问题根因 #

你遇到的计数翻倍问题是多表关联产生笛卡尔积导致的：

• 服务商2在connections_providers_categories关联表中对应2条分类数据，关联分类表后会生成2行结构不同但provider_id相同的记录
• 左连评论表时，这2行记录都会匹配到服务商2的同1条评论，最终分组统计时会把同一条评论统计2次，导致计数翻倍，平均值计算也会出现偏差。

可行解决方案 #

方案1：提前聚合评论统计结果（推荐） #

先通过子查询提前计算好每个服务商的评分平均值和评论数，再和其他表关联，完全避免笛卡尔积影响统计结果，SQL如下：

SELECT 
    prov.id, 
    prov.title, 
    prov_cat.title AS category, 
    review_stats.rating, 
    review_stats.count 
FROM connections_providers_categories conn 
INNER JOIN providers_categories prov_cat 
    ON prov_cat.id = conn.category_id 
INNER JOIN providers prov 
    ON prov.id = conn.provider_id 
LEFT JOIN (
    SELECT 
        provider_id, 
        AVG(rating) AS rating, 
        COUNT(rating) AS count 
    FROM reviews_providers 
    GROUP BY provider_id
) AS review_stats 
    ON review_stats.provider_id = conn.provider_id 
ORDER BY prov.title ASC

如果你的需求不需要展示分类，只需要每个服务商的统计结果，可以直接去掉分类相关的关联，语句会更简洁：

SELECT 
    prov.id, 
    prov.title, 
    AVG(reviews.rating) AS rating, 
    COUNT(reviews.rating) AS count 
FROM providers prov
LEFT JOIN reviews_providers reviews 
    ON reviews.provider_id = prov.id 
GROUP BY prov.id
ORDER BY prov.title ASC

方案2：使用DISTINCT去重计数（仅适合简单计数场景） #

如果只需要修正计数结果，可以在COUNT函数里加DISTINCT去重，但注意AVG函数不能这么修改，会导致平均值计算错误，因此仅适合不需要计算平均值的场景：

SELECT 
    prov.id, 
    prov.title, 
    prov_cat.title AS category, 
    AVG(reviews.rating) AS rating, 
    COUNT(DISTINCT reviews.id) AS count 
FROM connections_providers_categories conn 
INNER JOIN providers_categories prov_cat 
 ON prov_cat.id = conn.category_id 
LEFT JOIN reviews_providers reviews 
 ON reviews.provider_id = conn.provider_id 
INNER JOIN providers prov 
 ON prov.id = conn.provider_id 
GROUP BY prov.id 
ORDER BY prov.title ASC

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