连接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

如果两个演员演了同样数量的电影，我想得到他们两个呢？
一种方法是

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)

• • 查询**

给一个排名的基础上，电影的计数，然后加入演员表。

;with cte as (
    select num = dense_rank() over(
        order by count(actor_id) desc
    ), actor_id,  count(actor_id) as [count]
    from movies
    group by actor_id
)
select * from actors t1
where exists(
    select 1 from cte t2
    where t1.actor_id = t2.actor_id
    and t2.num = 1
);

或者最后部分可以如下所示。

select t1.actor_id, t1.actor_name
from #actors t1
join cte t2
on t1.actor_id = t2.actor_id
where t2.num = 1;

检查这个。
使用MAX：

select movie_name, max(TotalMovies) from 
        (

        select distinct a.movie_name,count(m.actor_id) as TotalMovies
        from movies m inner join actors a on a.actor_id   = m.actor_id
        group by movie_name  

        )a
        group by movie_name

用TotalMovies显示所有演员

select distinct a.movie_name,count(m.actor_id) as TotalMovies
        from movies m inner join actors a on a.actor_id   = m.actor_id
        group by movie_name  
        order by TotalMovies desc

使用TOP 1：

select distinct top 1 a.movie_name,count(m.actor_id) as TotalMovies
        from movies m inner join actors a on a.actor_id   = m.actor_id
        group by movie_name
        order by TotalMovies desc

放置另一个选择

select * from  dbo.actors where actor_id = (select max(y.x)
from (select count(actor_id) as x from movies group by actor_id) y)

试试这个：

SELECT count(actor.actor_id),actor.actor_name FROM movies join actor on actor.actor_id=movies.actor_id group by  actor.actor_id,actor.actor_name having count(movies.actor_id) =(
SELECT MAX(counted) FROM
(
    SELECT COUNT(actor_id) AS counted
    FROM movies
    GROUP BY actor_id
) AS counts
)

只需从子查询中选择actor_id，按降序排列并选择顶部记录。

SELECT TOP 1 COUNT(actor_id) AS x,actor_id AS z FROM 
               movies GROUP BY actor_id ORDER BY COUNT(actor_id) DESC

对于第2个问题

SELECT *,(SELECT actor_name FROM actors WHERE actor_id=Z.actor_id) AS Name FROM 
        (SELECT COUNT(actor_id) AS X,actor_id FROM movies GROUP BY actor_id) AS Z
WHERE X=(SELECT MAX(Y) FROM 
        (SELECT COUNT(actor_id) AS Y FROM 
            movies GROUP BY actor_id))

select concat(a.first_name, ' ', a.last_name) as full_name, count(f.actor_id) as num_movies
from actor a
inner join film_actor f
using (actor_id)
group by f.actor_id,1
order by count(f.actor_id) desc
limit 3;