如何查询参演电影数量最多及第三多的演员？附表结构及尝试SQL

嘿，我来帮你搞定这个查询问题！首先得先指出你原来SQL里的几个小问题，然后给你两种靠谱的解决方案。

你的原查询存在的问题 #

• GROUP BY 字段错误：你用了FILM_ACTOR.ACTOR_ID分组，但SELECT里调用了ACTOR表的FIRST_NAME和LAST_NAME，在大多数数据库的严格模式下这会报错——非聚合字段必须出现在GROUP BY列表里，应该按ACTOR.ACTOR_ID, FIRST_NAME, LAST_NAME来分组。
• 缺少数量统计：你没有计算每个演员的参演电影数，反而按Full_name排序，这完全没法筛选出参演最多的演员。
• 只能返回单个结果：TOP 1只能拿到第一个结果，没法同时获取第一和第三多的演员。

解决方案1：使用窗口函数（推荐，灵活处理排名） #

窗口函数是处理这类排名需求的最佳选择，我们可以先用CTE统计每个演员的参演数量，再用排名函数标记位次，最后筛选出第1和第3名。

情况1：不考虑并列排名（每个位次唯一）

如果希望即使有演员参演数量相同，也按姓名排序给出唯一的位次，用ROW_NUMBER()：

WITH ActorFilmCounts AS (
    SELECT 
        a.ACTOR_ID,
        CONCAT(a.FIRST_NAME, ' ', a.LAST_NAME) AS Full_Name,
        COUNT(fa.FILM_ID) AS Film_Count,
        -- 按参演数量降序、姓名升序排名
        ROW_NUMBER() OVER (ORDER BY COUNT(fa.FILM_ID) DESC, Full_Name ASC) AS RankNum
    FROM ACTOR a
    LEFT JOIN FILM_ACTOR fa ON a.ACTOR_ID = fa.ACTOR_ID
    GROUP BY a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME
)
SELECT Full_Name, Film_Count
FROM ActorFilmCounts
WHERE RankNum IN (1, 3)
ORDER BY RankNum ASC;

情况2：考虑并列排名（相同数量共享位次）

如果希望参演数量相同的演员拥有相同的排名（比如两个演员都是第一，下一个有效位次是第三），用RANK()：

WITH ActorFilmCounts AS (
    SELECT 
        a.ACTOR_ID,
        CONCAT(a.FIRST_NAME, ' ', a.LAST_NAME) AS Full_Name,
        COUNT(fa.FILM_ID) AS Film_Count,
        -- 按参演数量降序排名，相同数量共享位次
        RANK() OVER (ORDER BY COUNT(fa.FILM_ID) DESC) AS RankNum
    FROM ACTOR a
    LEFT JOIN FILM_ACTOR fa ON a.ACTOR_ID = fa.ACTOR_ID
    GROUP BY a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME
)
SELECT Full_Name, Film_Count, RankNum
FROM ActorFilmCounts
WHERE RankNum IN (1, 3)
ORDER BY RankNum ASC;

解决方案2：用子查询统计后筛选 #

如果你用的数据库不支持CTE或窗口函数，可以先统计每个演员的参演数，再通过子查询筛选位次：

SELECT 
    CONCAT(a.FIRST_NAME, ' ', a.LAST_NAME) AS Full_Name,
    Film_Count
FROM (
    SELECT 
        a.ACTOR_ID,
        COUNT(fa.FILM_ID) AS Film_Count,
        (SELECT COUNT(DISTINCT COUNT(fa2.FILM_ID)) 
         FROM FILM_ACTOR fa2 
         GROUP BY fa2.ACTOR_ID 
         HAVING COUNT(fa2.FILM_ID) > COUNT(fa.FILM_ID)) + 1 AS RankNum
    FROM ACTOR a
    LEFT JOIN FILM_ACTOR fa ON a.ACTOR_ID = fa.ACTOR_ID
    GROUP BY a.ACTOR_ID
) AS RankedActors
JOIN ACTOR a ON RankedActors.ACTOR_ID = a.ACTOR_ID
WHERE RankNum IN (1, 3)
ORDER BY RankNum ASC;

不过这个方法的效率不如窗口函数，更推荐用第一种方案。

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