如何查询各影视类型中参演作品数量最多的演员

嘿，这就来帮你搞定这个查询需求！先明确我们的两张数据源表：

表结构 #

genres表 #

genre_id | genre_name
---------|-----------
1        | comedy
2        | horror
3        | action

actors表 #

actor_name | genre_id
-----------|---------
stef       | 2
panos      | 2
bill       | 2
panos      | 2
bill       | 3
stef       | 3
panos      | 3
bill       | 3
stef       | 1
stef       | 1

需求说明 #

我们需要查询每个影视类型中参演该类型作品数量最多的演员，最终结果要包含类型名称、演员名称以及对应的参演次数。

解决方案（SQL语句） #

这里用窗口函数来实现，思路是先统计次数，再按类型排名取第一：

-- 先统计每个演员在各类型的参演次数
WITH actor_genre_counts AS (
    SELECT 
        g.genre_name,
        a.actor_name,
        COUNT(*) AS count
    FROM actors a
    JOIN genres g ON a.genre_id = g.genre_id
    GROUP BY g.genre_name, a.actor_name
),
-- 给每个类型内的演员按参演次数降序排名
ranked_actors AS (
    SELECT 
        genre_name,
        actor_name,
        count,
        ROW_NUMBER() OVER (PARTITION BY genre_name ORDER BY count DESC) AS rn
    FROM actor_genre_counts
)
-- 筛选出每个类型排名第一的演员
SELECT genre_name, actor_name, count
FROM ranked_actors
WHERE rn = 1
ORDER BY genre_name;

代码解释 #

1. actor_genre_counts CTE：关联actors和genres表，按类型和演员分组，统计每个演员在对应类型的参演次数。
2. ranked_actors CTE：使用ROW_NUMBER()窗口函数，以genre_name为分组依据，对每个组内的演员按参演次数从高到低排名。
3. 最后一步：筛选出排名为1的记录，就是每个类型中参演次数最多的演员，再按类型名称排序输出。

预期查询结果 #

genre_name | actor_name | count
-----------|------------|------
comedy     | stef       | 2
horror     | panos      | 2
action     | bill       | 2

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