编写一个SQL查询,根据与每个类别相关联的电影数量,生成排名前5的类别列表。查询结果必须包含以下列:
排名-类别的排名;类别-类别的名称;电影-属于该类别的电影数量。结果应该首先按排名按升序排序,然后按名称的字母顺序排序(对于具有相同排名的任何类别)。
我想知道如何让输出具有正确的顺序。
SELECT rank() OVER (ORDER BY count(film_id) DESC),name as
Category,count(film_id) as films
FROM Category
NATURAL JOIN Film NATURAL JOIN Film_Category
group by name
LIMIT 5期望值:
+------+-----------+-------+
| rank | category | films |
+------+-----------+-------+
| 1 | Horror | 9 |
| 2 | Action | 4 |
| 2 | Foreign | 4 |
| 2 | New | 4 |
| 5 | Animation | 3 |
+------+-----------+-------+实际:
| rank | category | films |
+------+-----------+-------+
| 1 | Horror | 9 |
| 2 | New | 4 |
| 2 | Action | 4 |
| 2 | Foreign | 4 |
| 5 | Animation | 3 |
+------+-----------+-------+发布于 2019-10-03 13:58:53
在LIMIT子句之前添加以下内容,以获得所需的订单:
ORDER BY rank() OVER (ORDER BY count(film_id) DESC),
namehttps://stackoverflow.com/questions/58212121
复制相似问题