sql - 选择播放电影次数最多的 Actor

Question

我有 2 个表。1)dbo.movi​​es

+------------+-------------+------------+
| movie_id   | movie_name  | actor_id   | 
+------------+-------------+------------+
| 1          | name1       |     1      | 
| 2          | name2       |     1      | 
| 3          | name3       |     4      | 
| 4          | name4       |     2      |
| 5          | name5       |     1      |
| 6          | name6       |     5      |
| 7          | name7       |     3      |
+------------+-------------+------------+

2) dbo.actors

+------------+-------------+
| actor_id   | actor_name  | 
+------------+-------------+
| 1          | name1       | 
| 2          | name2       |
| 3          | name3       |
| 4          | name4       |
| 5          | name5       |
+------------+-------------+

我想SELECT在电影中播放次数最多的actor_id和actor_name。

虽然我知道如何找到一个 Actor 演过最多的电影，但我找不到那个 Actor 是谁:

select max(y.x)
from (select count(actor_id) as x from movies group by actor_id) y

Answer 1

加入actors表获取姓名，按个数排序，只取第一条记录

select top 1 a.actor_name, count(*) as cnt
from actors a
join movies m on a.actor_id = m.actor_id
group by a.actor_id, a.actor_name
order by count(*) desc 

如果 2 位 Actor 出演了相同数量的电影，而我想同时获得他们两个，该怎么办？

一种方法是

select a.actor_name, count(*) as cnt
from actors a
join movies m on a.actor_id = m.actor_id
group by a.actor_id, a.actor_name
having count(*) = (select top 1 count(*) max_cnt from movies group by actor_id order by count(*) desc)