我正在尝试查询每十年最高评级的电影(或电影是否有2个最高(最高)评级)。我就快做到了，我唯一的问题是，如果十年内有两部电影(评级相同(最高评级))，它不会质疑它。我尝试了很多不同的方法，但似乎都不管用。到目前为止，我得到了

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

我的架构如下所示：

标题

title_id
title
premiered -> this is the year of movie's release

评级

title_id
rating

我不确定如何才能获得每十年(Sqlite)最大(评级)的所有事件。我想要的结果就是得到这样的东西

1920    The Kid 8.3
1920    Another_movie_with_matching_max(rating) 8.3

编辑：Jarlh建议使用子查询来获取每十年的最高评级。我想通了

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

它不能按预期工作