MySQL中如何获取各年份发布量最高的Netflix评级分组首行？

方法一：用窗口函数（MySQL 8.0及以上可用） #

这是最简洁的方式，利用ROW_NUMBER()窗口函数给每个年份内的评级分组按数量排序，直接取排名第一的记录。如果同一年有多个评级发布量并列最高，换成RANK()就能返回所有并列结果。

WITH ranked_ratings AS (
    SELECT 
        release_year,
        rating,
        type,
        COUNT(rating) AS rating_amount,
        -- 按年份分组，按发布量降序排名
        ROW_NUMBER() OVER (PARTITION BY release_year ORDER BY COUNT(rating) DESC) AS rn
    FROM netflix_condense
    WHERE country = 'United States'
    GROUP BY release_year, rating, type
)
SELECT release_year, rating, type, rating_amount
FROM ranked_ratings
WHERE rn = 1
ORDER BY release_year DESC;

方法二：子查询关联（兼容MySQL 5.x旧版本） #

如果你的MySQL版本不支持窗口函数，就用嵌套子查询先算出每年的最高发布量，再关联回分组结果找到对应评级。

SELECT 
    r.release_year,
    r.rating,
    r.type,
    r.rating_amount
FROM (
    -- 先算出所有年份-评级-类型的发布量
    SELECT 
        release_year,
        rating,
        type,
        COUNT(rating) AS rating_amount
    FROM netflix_condense
    WHERE country = 'United States'
    GROUP BY release_year, rating, type
) r
INNER JOIN (
    -- 再算出每个年份的最高发布量
    SELECT 
        release_year,
        MAX(rating_amount) AS max_amount
    FROM (
        SELECT 
            release_year,
            COUNT(rating) AS rating_amount
        FROM netflix_condense
        WHERE country = 'United States'
        GROUP BY release_year, rating, type
    ) t
    GROUP BY release_year
) m ON r.release_year = m.release_year AND r.rating_amount = m.max_amount
ORDER BY r.release_year DESC;

额外说明 #

• 要是你不需要区分电影/剧集（type字段），可以把GROUP BY里的type去掉，就能拿到每年整体发布量最高的评级。
• 测试时可以加个WHERE release_year >= 2020缩小范围，快速验证结果是否符合预期。

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