如何修改SQL将各电影类型的Top参演演员结果限制为1条？

嘿，我来帮你搞定这个问题！你当前的SQL确实能定位到各类型中参演电影数量最多的演员，但当有多个演员并列第一时，就会返回多条同类型的记录。要实现每个类型只输出1条结果，咱们可以用窗口函数来给分组内的记录排序，筛选出排名第一的那条就行。

优化后的SQL语句 #

WITH actor_genre_stats AS (
    -- 第一步：计算每个演员在对应类型的参演电影数量
    SELECT 
        g.genre_name,
        a.actor_id,
        COUNT(*) AS movie_count
    FROM genre g
    INNER JOIN movie_has_genre mhg ON mhg.genre_id = g.genre_id
    INNER JOIN movie m ON mhg.movie_id = m.movie_id
    INNER JOIN role r ON m.movie_id = r.movie_id
    INNER JOIN actor a ON a.actor_id = r.actor_id
    GROUP BY g.genre_name, a.actor_id
),
ranked_actors AS (
    -- 第二步：给每个类型内的演员按参演数排名，并列时选ID更小的
    SELECT 
        genre_name,
        actor_id,
        movie_count,
        ROW_NUMBER() OVER (
            PARTITION BY genre_name 
            ORDER BY movie_count DESC, actor_id ASC
        ) AS rank_num
    FROM actor_genre_stats
)
-- 第三步：只取每个类型排名第一的演员
SELECT 
    genre_name, 
    actor_id, 
    movie_count AS max_value
FROM ranked_actors
WHERE rank_num = 1
ORDER BY max_value DESC;

关键逻辑说明 #

1. actor_genre_stats CTE：这部分和你原始SQL的内层聚合逻辑一致，先算出每个演员在每个类型下的参演电影总数，用CTE来存储结果，让代码更易读。
2. ROW_NUMBER()窗口函数：
   • PARTITION BY genre_name：按电影类型分组，保证每个类型单独排序。
   • ORDER BY movie_count DESC, actor_id ASC：先按参演数量从多到少排，数量相同的话，按演员ID从小到大排（这样就能像你示例里那样，Action类型选ID最小的292028）。
   • 这个函数会给每个分组内的记录分配唯一的序号，排名第一的序号就是1。
3. 最终筛选：只保留rank_num = 1的记录，就能实现每个类型仅返回1条结果。

可选调整 #

如果你以后需要保留所有并列第一的演员，只需要把ROW_NUMBER()换成RANK()就行——但根据你的需求，ROW_NUMBER()是最适合的。另外，你也可以把排序规则换成按演员名字排序，只需要把ORDER BY里的actor_id ASC改成a.name ASC（记得在第一个CTE里加上a.name并加入GROUP BY）。

这样修改后，就能得到你期望的输出结果啦！

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