基于GROUP BY与JOIN获取各年龄段最受评分电影的SQL查询问题

表定义 #

users表 #

id | age | gender | occupation | zipcode

ratings表 #

userid | movieid | rating | ts

要实现每个年龄段评分次数最多的电影查询，以下是可行方案：

方案一：子查询关联法 #

先统计各年龄段每部电影的评分次数，再匹配对应年龄段的最高评分次数，筛选出符合条件的记录：

SELECT t1.age, t1.movieid, t1.mcount
FROM (
    SELECT u.age, r.movieid, COUNT(*) AS mcount
    FROM ratings r
    JOIN users u ON u.id = r.userid
    GROUP BY u.age, r.movieid
) t1
JOIN (
    SELECT age, MAX(mcount) AS max_count
    FROM (
        SELECT u.age, r.movieid, COUNT(*) AS mcount
        FROM ratings r
        JOIN users u ON u.id = r.userid
        GROUP BY u.age, r.movieid
    ) t2
    GROUP BY age
) t3 ON t1.age = t3.age AND t1.mcount = t3.max_count;

方案二：窗口函数法（适用于支持窗口函数的SQL环境，如MySQL 8+、PostgreSQL） #

利用窗口函数直接对各年龄段的电影评分次数排名，取排名第一的结果：

WITH age_movie_counts AS (
    SELECT 
        u.age, 
        r.movieid, 
        COUNT(*) AS mcount,
        RANK() OVER (PARTITION BY u.age ORDER BY COUNT(*) DESC) AS rank_num
    FROM ratings r
    JOIN users u ON u.id = r.userid
    GROUP BY u.age, r.movieid
)
SELECT age, movieid, mcount
FROM age_movie_counts
WHERE rank_num = 1;

注：RANK()会保留并列第一的电影，若想只保留一条（随机）可改用ROW_NUMBER()

原查询问题说明 #

你之前尝试的关联查询错误在于：WHERE mc2=t2.mc放在了GROUP BY之前，此时聚合函数count(*) as mc2尚未计算完成，无法在WHERE子句中直接引用。正确的做法是先完成聚合统计，再通过关联或HAVING子句筛选。

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