如何仅用INNER JOIN合并子查询表并计算SQLite平均得分？

一、满足INNER JOIN要求的解法 #

按照课程要求用INNER JOIN的话，需要先通过匹配的id或name关联两个表，再合并总分和次数计算平均。针对你给出的示例数据，SQL如下：

SELECT 
    t1.Id1 AS id,
    t1.name,
    ROUND((t1.score_total + t2.score_total) * 1.0 / (t1.count_score + t2.count_score), 2) AS average_score
FROM 表1 t1
INNER JOIN 表2 t2 ON t1.Id1 = t2.id2;

关键说明： #

• 用t1.Id1 = t2.id2作为关联条件，确保是同一个用户的记录合并
• 乘以1.0是为了避免SQLite的整数除法（直接整数相除会丢弃小数部分）
• ROUND(...,2)用来保留两位小数，和你示例中John的13.75结果一致

对应你实际使用的good_collaboration和movie_cast表，修正后的SQL如下（解决你原有代码的问题）：

SELECT 
    t1.cast_member_id,
    t1.cast_name,
    ROUND((t1.collab_score + t2.collab_score) * 1.0 / (t1.collab_count + t2.collab_count), 2) AS average_score
FROM (
    SELECT
      cast_member_id1 AS cast_member_id,
      cast_name,
      SUM(average_movie_score) as collab_score, -- 保留数值类型，不要转字符串
      COUNT(cast_member_id1) AS collab_count
    FROM good_collaboration
    JOIN movie_cast ON cast_member_id1 = movie_cast.cast_id
    GROUP BY cast_member_id1, cast_name -- 必须包含cast_name，避免分组错误
) t1 
INNER JOIN (
    SELECT
      cast_member_id2 AS cast_member_id,
      cast_name,
      SUM(average_movie_score) as collab_score,
      COUNT(cast_member_id2) AS collab_count -- 修正为统计当前子查询的id字段
    FROM good_collaboration
    JOIN movie_cast ON cast_member_id2 = movie_cast.cast_id
    GROUP BY cast_member_id2, cast_name
) t2 ON t1.cast_member_id = t2.cast_member_id; -- 必须添加关联条件，匹配同一用户

二、更灵活的替代方案（不限INNER JOIN） #

如果不需要严格限制只用INNER JOIN，推荐用UNION ALL合并两个表的记录后再聚合，这样能保留所有用户（包括只在单个表中出现的Peter、Cassidy），逻辑更简洁：

-- 针对示例表的完整解法
SELECT 
    id,
    name,
    ROUND(SUM(score_total) * 1.0 / SUM(count_score), 2) AS average_score
FROM (
    SELECT Id1 AS id, name, score_total, count_score FROM 表1
    UNION ALL
    SELECT id2 AS id, name, score_total, count_score FROM 表2
) combined_data
GROUP BY id, name;

这个方案不仅能得到John、David的结果，还能输出Peter（50/2=25）和Cassidy（120/4=30）的平均得分，更符合完整的统计需求。

三、你原有SQL的问题分析 #

1. 缺少JOIN关联条件：两个子查询直接INNER JOIN但没有指定ON子句，导致生成笛卡尔积，结果完全错误
2. 数值转字符串导致计算异常：printf('%.2f', SUM(...))将数值转为字符串，后续除法会触发隐式转换，容易得到错误结果，应该保留数值类型计算
3. 统计字段错误：第二个子查询中COUNT(cast_member_id1)应该改为COUNT(cast_member_id2)，否则统计的是第一个表的id次数，逻辑不符
4. 分组不完整：GROUP BY子句中未包含cast_name，在SQLite开启ONLY_FULL_GROUP_BY模式时会报错，也不符合SQL标准

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