MySQL Sakila选择影片最多的演员

Question

.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. 选择每个演员n次(n=在电影中出现）。我的查询：选择actor.first_name,actor.last_name FROM actor.INNER连接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
+------------+-------------+

结果：演员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     |
+----------+---------+

但我要的是：出演电影最多的演员：

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

任何帮助都是非常感谢的。谢谢你！

Answer 1

如果无法在db上运行查询，我会执行以下操作：

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 ;

未经测试