如何通过SQL查询电影平均评分的最大值及对应影片标题？

我来帮你解决这个查询平均评分最高影片的问题！首先得指出你原来的SQL存在的语法逻辑问题，再给你几个可行的解决方案：

原SQL的问题 #

• 最后一行的WHERE子句写法不对：(select max(avgstrs))没有和外层的avgstrs做比较，数据库根本没法识别这个条件的意图。
• 另外，GROUP BY R1.mID的时候最好同时带上Movie.title分组，虽然理论上mID是影片的主键，但加上title能避免数据异常导致的分组错误，让查询更严谨。

方案1：子查询匹配最大平均分 #

这种写法先算出每部影片的平均评分，再找出所有影片中的最高平均分，最后把平均分等于最高分的影片筛选出来：

SELECT title, avgstrs
FROM (
    -- 计算每部影片的平均评分
    SELECT 
        AVG(R1.stars) AS avgstrs,
        Movie.title
    FROM Movie
    INNER JOIN Rating R1 ON R1.mID = Movie.mID
    GROUP BY R1.mID, Movie.title
) AS Avrg
-- 匹配平均评分等于所有影片中的最高分
WHERE avgstrs = (
    SELECT MAX(avg_stars)
    FROM (
        SELECT AVG(stars) AS avg_stars
        FROM Rating
        GROUP BY mID
    ) AS max_avg_sub
);

方案2：用窗口函数（更灵活） #

如果存在多部影片并列平均最高分的情况，这个方案可以一次性返回所有并列的影片，比方案1更实用：

SELECT title, avgstrs
FROM (
    SELECT 
        Movie.title,
        AVG(R1.stars) AS avgstrs,
        -- 按平均评分降序排名，并列最高分的影片排名都是1
        RANK() OVER (ORDER BY AVG(R1.stars) DESC) AS rating_rank
    FROM Movie
    INNER JOIN Rating R1 ON R1.mID = Movie.mID
    GROUP BY Movie.mID, Movie.title
) AS ranked_movies
WHERE rating_rank = 1;

这里说下RANK()和ROW_NUMBER()的区别：用RANK()会保留并列排名，所有最高分影片都会被返回；如果用ROW_NUMBER()，则只会随机返回其中一部（具体取决于数据库的排序规则），你可以根据自己的需求选择。

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