mysql - 根据排名打印每年排名前 5 的剪辑

Question

我需要打印每年的内容:电影的标题、年份以及前 5 个剪辑的年份排名(基于其排名)。

所以，我需要找到每年评分最高的 5 部电影。我已经尝试过按年份分组(releases.date)，但我还需要标题，并且需要获取每组的第 N 顶行。

CLIPS [ id, title ]
RELEASES [ id_clip, date ]
RANKS [ id_clip, nr_votes, ranking ]

有什么提示吗？我正在使用MySQL。我的剪辑表中也有年份。

mysql> select * from clip LIMIT 5;
+--------+-----------------------+------+-----------+
| idclip | title                 | year | clip_type |
+--------+-----------------------+------+-----------+
|      4 | !Women Art Revolution | 2010 |         0 |
|      7 | #1                    | 2005 |         0 |
|      8 | #1 (I)                | 2010 |         2 |
|      9 | #1 (II)               | 2010 |         2 |
|     12 | #1 Cheerleader Camp   | 2010 |         2 |
+--------+-----------------------+------

mysql> select * from releasedates LIMIT 5;
+---------+---------------------+---------+
| id_clip | date                | country |
+---------+---------------------+---------+
|       4 | 2010-09-01 00:00:00 | Canada  |
| 1773811 | 2006-01-22 00:00:00 | USA     |
| 1773815 | 2006-02-12 00:00:00 | USA     |
| 1773818 | 2006-02-19 00:00:00 | USA     |
| 1773820 | 2006-01-22 00:00:00 | USA     |
+---------+---------------------+---------+
5 rows in set (0,00 sec)

mysql> select * from ratings LIMIT 5;
+---------+-------+---------+
| id_clip | votes | ranking |
+---------+-------+---------+
|       4 |     8 | 8.0     |
| 1773811 |    51 | 4.5     |
| 1773839 |   753 | 6.3     |
| 1773843 |    32 | 6.9     |
| 1773844 |    18 | 7.1     |
+---------+-------+---------+
5 rows in set (0,00 sec)

Answer 1

假设 Clips 中的 id 列与版本中的 id_clip 列类似，您可以尝试一下吗？目前我无法测试，但我认为这应该可行

select Title,Year(date) ,
RANK() OVER(PARTITION BY Year(Date) ORDER BY ranking)
from Clips c
Releases r on c.id=r.id_clip
join Ranks rank on r.id_clip=rank.id_clip