MySQL多表关联查询问题：按电影类型统计12月有效平均评分

问题场景 #

我使用MySQL数据库，拥有两张表：

• types表：存储电影ID与类型
• ratings表：存储电影ID、评分及timestamp时间戳

需求：通过movieId关联两张表，统计每种电影类型的平均评分，要求仅筛选出单部电影至少有10条12月评分的类型，并按平均评分从高到低排序。

原无法执行的SQL语句 #

SELECT DISTINCT T.type, AVG(R.rating) FROM types AS T
INNER JOIN ratings AS R ON T.movieId = R.movieId
WHERE R.timestamp LIKE (
    SELECT FROM_UNIXTIME(R.timestamp,'%M') AS Month FROM ratings
    GROUP BY Month
    HAVING Month = 'December')
GROUP BY T.type
HAVING COUNT(R.rating) >=10
ORDER BY AVG(R.rating) DESC;

原SQL的问题点 #

• WHERE子句逻辑错误：用LIKE匹配子查询结果完全不合理，R.timestamp是数值型时间戳，子查询返回的是字符串"December"，类型不匹配；且子查询的分组筛选逻辑冗余，直接判断月份即可。
• 需求理解偏差：原HAVING子句COUNT(R.rating) >=10统计的是该类型下所有电影的总评分条数，而非需求要求的单部电影至少有10条12月评分。
• 多余的DISTINCT：按T.type分组后，结果中类型不会重复，无需使用DISTINCT。

修正后的SQL语句 #

-- 先筛选出12月评分条数≥10的电影ID
WITH qualified_movies AS (
    SELECT movieId
    FROM ratings
    WHERE MONTH(FROM_UNIXTIME(timestamp)) = 12 -- 用数字判断12月，避免英文本地化/大小写问题
    GROUP BY movieId
    HAVING COUNT(*) >= 10
)
SELECT 
    t.type,
    ROUND(AVG(r.rating), 2) AS Avg_Rating -- 保留两位小数匹配预期结果格式
FROM types t
INNER JOIN ratings r ON t.movieId = r.movieId
INNER JOIN qualified_movies qm ON t.movieId = qm.movieId
WHERE MONTH(FROM_UNIXTIME(r.timestamp)) = 12 -- 仅统计12月的评分数据
GROUP BY t.type
ORDER BY Avg_Rating DESC;

修正说明 #

1. 精准筛选符合条件的电影：用CTE（qualified_movies）先提取出12月评分记录≥10条的movieId，完全匹配“单部电影至少有10条12月评分”的要求。
2. 限定统计范围：关联时仅保留符合条件的电影，同时在WHERE中限定只统计12月的评分，确保平均评分仅计算有效数据。
3. 避免本地化问题：用MONTH()函数提取月份数字（12），替代英文月份名，防止因数据库本地化设置或大小写导致的匹配错误。
4. 优化结果格式：移除多余的DISTINCT，给平均评分字段起别名Avg_Rating，并用ROUND()保留两位小数，与预期结果格式一致。

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