如何找到出演电影次数最多、最忙的演员

Question

得到2张桌子

表1-执行元：ACTOR_ID、FIRST_NAME、LAST_NAME、LAST_UPDATE

表2-FILM_ACTOR:ACTOR_ID、FILM_ID、LAST_UPDATE

我已经尝试了以下关于寻找谁曾在最多的电影中扮演

select top 1 concat(ACTOR.FIRST_NAME, ACTOR.LAST_NAME) as Full_name
from ACTOR
left join FILM_ACTOR on ACTOR.ACTOR_ID = FILM_ACTOR.ACTOR_ID
group by FILM_ACTOR.ACTOR_ID
order by Full_name desc;

Answer 1

您可以执行聚合：：

select a.*
from actor a 
where a.actor_id = (select top (1) fm.actor_id 
                    from film_actor fm 
                    group by fm.actor_id 
                    order by count(*) desc
                   );