SQL分组查询各电影类型参演最多N位演员的问题求助

我来帮你搞定这个需求！你的第一个SQL已经正确统计出了每个类型里每位演员的参演作品数量，但后续的嵌套查询思路有问题——外层查询没法直接引用内层表的字段，而且分组方式也没法保留同一类型里并列第一的演员。

问题分析 #

你之前的嵌套查询错误在于：外层查询直接使用genre.genre_name，但外层并没有关联genre表，应该引用内层子查询的别名apotelesmata里的字段；另外，按genre.genre_name分组后只会返回每个类型的一条记录，没法保留像Action类型里两个参演数都是3的演员。

正确解决方案（MySQL 8.0+ 推荐用窗口函数） #

窗口函数是处理这类分组排名需求最简洁的方式，我们可以用RANK()函数给每个类型下的演员按参演数排名，然后筛选出排名第一的（包括并列）：

WITH actor_genre_counts AS (
    SELECT 
        g.genre_name,
        a.actor_id,
        COUNT(DISTINCT m.movie_id) AS movie_count  -- 用DISTINCT避免同一演员在同一电影多角色重复计数，不需要的话可以去掉
    FROM genre g
    JOIN movie_has_genre mhg ON g.genre_id = mhg.genre_id
    JOIN movie m ON mhg.movie_id = m.movie_id
    JOIN role r ON m.movie_id = r.movie_id
    JOIN actor a ON r.actor_id = a.actor_id
    GROUP BY g.genre_name, a.actor_id
),
ranked_actors AS (
    SELECT 
        genre_name,
        actor_id,
        movie_count,
        RANK() OVER (PARTITION BY genre_name ORDER BY movie_count DESC) AS rnk
    FROM actor_genre_counts
)
SELECT genre_name, actor_id, movie_count
FROM ranked_actors
WHERE rnk = 1
ORDER BY genre_name, movie_count DESC;

代码说明：

1. actor_genre_counts CTE：这部分和你第一个SQL逻辑一致，统计每个类型下每位演员的参演作品数（加DISTINCT是为了避免同一演员在同一部电影里有多个角色时重复计数，如果你的需求是统计角色数量，直接去掉DISTINCT即可）。
2. ranked_actors CTE：用RANK()窗口函数，按genre_name分组（PARTITION BY），然后按参演数降序排名。这样同一类型里参演数最多的演员，排名都会是1，包括并列的情况。
3. 最后筛选出rnk = 1的记录，就是你要的每个类型下参演作品最多的演员列表。

兼容MySQL 5.x的方案（无窗口函数） #

如果你用的是不支持窗口函数的旧版MySQL，可以用关联子查询的方式实现：

SELECT 
    g.genre_name,
    a.actor_id,
    COUNT(DISTINCT m.movie_id) AS movie_count
FROM genre g
JOIN movie_has_genre mhg ON g.genre_id = mhg.genre_id
JOIN movie m ON mhg.movie_id = m.movie_id
JOIN role r ON m.movie_id = r.movie_id
JOIN actor a ON r.actor_id = a.actor_id
GROUP BY g.genre_name, a.actor_id
HAVING COUNT(DISTINCT m.movie_id) = (
    SELECT MAX(agc.count_movies)
    FROM (
        SELECT COUNT(DISTINCT m2.movie_id) AS count_movies
        FROM movie_has_genre mhg2
        JOIN movie m2 ON mhg2.movie_id = m2.movie_id
        JOIN role r2 ON m2.movie_id = r2.movie_id
        WHERE mhg2.genre_id = g.genre_id
        GROUP BY r2.actor_id
    ) agc
)
ORDER BY g.genre_name, movie_count DESC;

代码说明：

外层分组统计后，用HAVING子句判断当前演员的参演数是否等于该类型下所有演员的最大参演数，从而筛选出每个类型里的top演员。

这两个方案都能得到你期望的输出结果，比如Action类型里的两位参演数为3的演员都会被保留。

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