MySQL中一对一字段求平均与一对多字段求和的关联异常问题

嘿，我完全懂你碰到的这个坑！当你把核心的person表和一对多关联的person_co表做JOIN时，person里的单条记录会和它对应的所有person_co记录匹配，相当于同一个人的score被重复计算了N次（N就是这个人关联的co数量）。这样一来，AVG(score)算出来的就不是真实的人均分数平均值，而是被“重复加权”后的结果，而SUM(co)倒是没问题，因为本来就要把所有co加起来。

给你两种靠谱的解决思路，核心都是把平均值和总和分开计算再合并，避免score被重复统计：

方法一：用CTE拆分计算（可读性最强） #

把两个计算逻辑拆成独立的公共表，最后再关联结果：

-- 第一步：单独计算每个类别的真实score平均值（只关联person和person_cat）
WITH category_avg_score AS (
    SELECT
        pc.cat,
        AVG(p.score) AS avg_score
    FROM person p
    JOIN person_cat pc ON p.person_id = pc.person_id
    GROUP BY pc.cat
),
-- 第二步：计算每个类别的co总和（关联所有三张表）
category_total_co AS (
    SELECT
        pc.cat,
        SUM(pco.co) AS total_co
    FROM person p
    JOIN person_co pco ON p.person_id = pco.person_id
    JOIN person_cat pc ON p.person_id = pc.person_id
    GROUP BY pc.cat
)
-- 最后合并两个结果
SELECT
    cas.cat,
    cas.avg_score,
    ctc.total_co
FROM category_avg_score cas
JOIN category_total_co ctc ON cas.cat = ctc.cat;

方法二：用子查询嵌入平均值计算 #

如果不习惯CTE，也可以在主查询里用子查询单独拉取每个类别的平均score：

SELECT
    pc.cat,
    -- 子查询仅针对当前类别计算真实平均score
    (SELECT AVG(p_inner.score)
     FROM person p_inner
     JOIN person_cat pc_inner ON p_inner.person_id = pc_inner.person_id
     WHERE pc_inner.cat = pc.cat) AS avg_score,
    SUM(pco.co) AS total_co
FROM person p
JOIN person_co pco ON p.person_id = pco.person_id
JOIN person_cat pc ON p.person_id = pc.person_id
GROUP BY pc.cat;

这两种方法的关键都是解耦平均值和总和的计算逻辑：平均值只依赖person和person_cat的关联，保证每个person的score只被统计一次；总和则正常关联person_co累加所有co值，最后合并结果就能得到准确的数值了。

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