SQL查询中SUM与COUNT错误相乘问题的修复方法咨询

问题原因 #

当同时左连接dictionary_ratings和terms两张表时，会产生笛卡尔积：每个评分记录会与该字典下的所有词条记录配对，导致评分总和被重复计算，重复次数等于该字典的词条数量，最终得到错误的放大值。

修复方法 #

方法一：先聚合子表再连接主表

先分别对评分表和词条表按字典ID聚合，得到每个字典的独立评分总和与词条数，再关联主表，彻底避免笛卡尔积：

SELECT 
    d.title,
    COALESCE(r.total_rating, 0) AS rating_count,
    COALESCE(t.total_terms, 0) AS term_count
FROM dictionaries d
LEFT JOIN (
    SELECT dictionary_id, SUM(rating) AS total_rating
    FROM dictionary_ratings
    GROUP BY dictionary_id
) r ON d.id = r.dictionary_id
LEFT JOIN (
    SELECT dictionary_id, COUNT(id) AS total_terms
    FROM terms
    GROUP BY dictionary_id
) t ON d.id = t.dictionary_id
ORDER BY d.created_at DESC

• COALESCE用于处理无评分/词条的字典，返回0而非NULL，保证结果一致性。

方法二：使用窗口函数（适用于支持窗口函数的数据库）

通过窗口函数按字典ID分区计算聚合值，再去重得到唯一结果：

SELECT 
    DISTINCT d.title,
    COALESCE(SUM(r.rating) OVER (PARTITION BY d.id), 0) AS rating_count,
    COALESCE(COUNT(t.id) OVER (PARTITION BY d.id), 0) AS term_count
FROM dictionaries d
LEFT JOIN dictionary_ratings r ON d.id = r.dictionary_id
LEFT JOIN terms t ON d.id = t.dictionary_id
ORDER BY d.created_at DESC

• PARTITION BY d.id确保每个字典的聚合计算独立进行，DISTINCT去除连接产生的重复行。

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