MySQL Sakila 选择出演电影最多的 Actor

Question

我需要选择出现次数最多的电影的 Actor 。我使用的是 sakila 数据库 1.0:

.mwb (MySQL Workbench): https://www.sendspace.com/file/i0z82j
.sql (schema): https://www.sendspace.com/file/vd3hnu
.sql (data): https://www.sendspace.com/file/gbp9ri

我已经有两个查询

1. 它会选择每个 Actor n 次(n=在电影中的出场次数)。我的查询:SELECT actor.first_name, actor.last_name来自 Actor INNER JOIN 电影_ Actor ON actor.actor_id = film_actor.actor_id;

结果:

+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| PENELOPE   |GUINESS      | //4 Films in this examle
| PENELOPE   |GUINESS      |
| PENELOPE   |GUINESS      |
| PENELOPE   |GUINESS      |
| NICK       |WAHLBERG     | //5 Films in this examle
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| NICK       |WAHLBERG     |
| ED         |CHASE        | //5 Films in this examle
| ED         |CHASE        |
| ED         |CHASE        |
| ED         |CHASE        |
| ED         |CHASE        |
| JENNIFER   |DAVIS        | //4 Films in this examle
| JENNIFER   |DAVIS        |
| JENNIFER   |DAVIS        |
| JENNIFER   |DAVIS        |
| BETTE      |NICHOLSON    | //3 Films in this examle
| BETTE      |NICHOLSON    |
| BETTE      |NICHOLSON    |
| ...        |...          | //Everyother Actor who appeared in a film
+------------+-------------+

选择特定 Actor 出演的每部电影。我的查询:选择 actor_id,film_id来自电影_ Actor 其中 actor_id = 1；

结果: Actor 1 出演了 19 部电影。

+----------+-------- +
| actor_id | film_id |
+----------+---------+
| 1        | 1       |
| 1        | 23      |
| 1        | 25      |
| 1        | 106     |
| 1        | 140     |
| 1        | 166     |
| 1        | 277     |
| 1        | 361     |
| 1        | 438     |
| 1        | 499     |
| 1        | 506     |
| 1        | 509     |
| 1        | 605     |
| 1        | 635     |
| 1        | 749     |
| 1        | 832     |
| 1        | 939     |
| 1        | 970     |
| 1        | 980     |
+----------+---------+

但我想要的是:出演影片最多的 Actor :

+------------+---------------+--------+
| first_name | last_name     | films  |
+------------+---------------+--------+
| NICK       | WAHLBERG      | 5      |
| ED         | CHASE         | 5      |
| ...        | ...           | ...    |
+------------+----------------+-------+

非常感谢任何帮助。谢谢!

Answer 1

如果无法在您的数据库上运行查询，我会这样做:

SELECT count(film_actor.actor_id), actor.first_name, actor.last_name 
FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY film_actor.actor_id ;

未经测试