mysql - sql - 对于每个 Actor ， Actor 的哪部电影被租用最多以及被租用了多少次

Question

在 Sakila示例数据库，如何获取每个 Actor 租用最多的电影及其数量？换句话说，如何获得关系的最高/最多出现次数及其计数？

我能够按 Actor 和电影对结果进行分组，但无法限制每个 Actor 的结果:

select concat(a.first_name, ' ', a.last_name ) as Actor, f.title as Movie, count(f.film_id) as Count
    from actor as a
        left join film_actor fa on a.actor_id = fa.actor_id
        inner join film f on fa.film_id = f.film_id
        left join inventory i on f.film_id = i.film_id
        left join rental r on i.inventory_id = r.inventory_id
    group by a.actor_id, f.film_id
    order by a.actor_id, Count desc

phpMyAdmin 有 Sakila 演示 here

期望的结果是:

    Actor                 Movie         Rent Count
NICK WAHLBERG       GOODFELLAS SALUTE   31
ED CHASE            FROST HEAD          30
PENELOPE GUINESS    GLEAMING JAWBREAKER 29

Answer 1

您可以使用 CTE ，然后选择与最大计数匹配的所有行(使用 ANY 谓词):

with q1 as (
    select
        concat(a.first_name, ' ', a.last_name ) as Actor,
        f.title as Movie,
        count(f.film_id) as Count
    from
        actor as a
        left join film_actor fa on a.actor_id = fa.actor_id
        inner join film f on fa.film_id = f.film_id
        left join inventory i on f.film_id = i.film_id
        left join rental r on i.inventory_id = r.inventory_id
    group by
        a.actor_id,
        f.film_id
)
select * from q1
where (q1.Actor, q1.Count) = any(select q2.Actor, max(q2.Count) from q1 q2 group by q2.Actor)
order by q1.Actor

您还可以使用相关子查询来代替 ANY，因此 where 子句将是:

where q1.Count = (select max(q2.Count) from q1 q2 where q1.Actor = q2.Actor)