我正在尝试查询每十年最高评级的电影(或电影是否有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
它不能按预期工作
1条答案
按热度按时间w51jfk4q1#
我想通了。必须使用IN()而不是将两个表连接在一起。