SQLite查询每个年代最高评分的全部影片问题

问题概述 #

需要查询每个年代评分最高的影片，若同年代有多部影片评分同为最高分，需全部返回。现有查询仅能返回每个年代的单部影片，无法满足需求。

现有查询语句 #

SELECT FLOOR(premiered / 10) * 10 AS Decades,
       title,
       rating
  FROM titles
       INNER JOIN
       ratings ON titles.title_id = ratings.title_id 
 GROUP BY decades

现有查询结果 #

1920    The Kid 8.3
1930    City Lights 8.5
1940    It's a Wonderful Life   8.6
1950    12 Angry Men    9
1960    The Good, the Bad and the Ugly  8.8
1970    The Godfather   9.2
1980    Star Wars: Episode V - The Empire Strikes Back  8.7
1990    The Shawshank Redemption    9.3
2000    The Lord of the Rings: The Return of the King   9
2010    Inception   8.8
2020    Jai Bhim    8.9

数据库表结构 #

titles表 #

title_id
title
premiered -> 影片上映年份

ratings表 #

title_id
rating

期望结果示例 #

1920    The Kid 8.3
1920    Another_movie_with_matching_max(rating) 8.3

已尝试的子查询及问题 #

尝试通过子查询获取每个年代的最高评分，但无法正确关联获取对应影片，写出的子查询如下：

SELECT FLOOR(premiered / 10) * 10 AS Decades,
       rating as rat
FROM ratings
     JOIN
     titles ON ratings.title_id = titles.title_id
GROUP BY decades
HAVING max(rating) 

后续尝试的关联查询未达到预期效果：

SELECT FLOOR(premiered / 10) * 10 AS Decades,
       title,
       rating
  FROM titles
       INNER JOIN
       ratings ON titles.title_id = ratings.title_id 
where decades and RATING = (
           SELECT FLOOR(premiered / 10) * 10,
                  rating as rat
             FROM ratings
                  JOIN
                  titles ON ratings.title_id = titles.title_id
            GROUP BY FLOOR(premiered / 10) * 10
           HAVING max(rating) 
           )
           GROUP BY decades

解决方案 #

方法一：子查询关联法（兼容所有SQLite版本） #

先计算每个年代的最高评分，再将原表与该结果关联，筛选出评分等于对应年代最高分的影片：

WITH decade_max_ratings AS (
    SELECT 
        FLOOR(t.premiered / 10) * 10 AS decades,
        MAX(r.rating) AS max_rating
    FROM titles t
    JOIN ratings r ON t.title_id = r.title_id
    GROUP BY decades
)
SELECT 
    FLOOR(t.premiered / 10) * 10 AS decades,
    t.title,
    r.rating
FROM titles t
JOIN ratings r ON t.title_id = r.title_id
JOIN decade_max_ratings dmr 
    ON FLOOR(t.premiered / 10) * 10 = dmr.decades 
    AND r.rating = dmr.max_rating
ORDER BY decades, title;

方法二：窗口函数法（SQLite 3.25+支持） #

利用RANK()窗口函数为每个年代的影片按评分排名，直接筛选出排名为1的影片（相同最高分的影片会被同时选中）：

SELECT decades, title, rating
FROM (
    SELECT 
        FLOOR(t.premiered / 10) * 10 AS decades,
        t.title,
        r.rating,
        RANK() OVER (PARTITION BY FLOOR(t.premiered / 10) * 10 ORDER BY r.rating DESC) AS rating_rank
    FROM titles t
    JOIN ratings r ON t.title_id = r.title_id
) ranked_movies
WHERE rating_rank = 1
ORDER BY decades, title;

说明 #

• 方法一兼容性更强，适用于所有版本的SQLite；
• 方法二代码更简洁，需要SQLite 3.25及以上版本（2018年发布）。

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