我需要每年打印:电影的标题,年度和排名在前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)
1条答案
按热度按时间eiee3dmh1#
假设clips中的id列与releases中的id\u clip列相似,您能试试这个吗,我现在不能测试这个,但是我认为这个应该可以