mysql - SQL从一部电影中选择参与电影次数较多的 Actor

Question

我是 SQL 新手，并且对这个查询很困难，所以我非常感谢一些帮助/建议:

表格看起来像

person: id_person name birth

movie: id_movie movie.title

participation: id_person id_movie

我正在尝试获取电影中每部电影的 ID 和名称，以及参与该电影最多的 Actor 的姓名(如果 2 个 Actor 按年龄/字母顺序排列相同)。

到目前为止我得到了:

SELECT 
  DISTINCT movie.id, movie.title, t.name, t.num_movies 
from 
 movie
   JOIN participation on participation.id_movie = movie.id
   JOIN person on participation.id_actor = person.id
   JOIN (
         SELECT 
           person.id as id, person.name as name
           , person.birth, count(movie.id) as num_movies 
        from person 
          JOIN participation on participation.id_actor = person.id 
           JOIN movie on movie.id=participation.id_movie 
        GROUP BY person.id 
        ORDER BY person.birth ASC, person.name ASC
      ) as t on t.id=person.id
ORDER BY t.num_movies DESC

这为我提供了所有 Actor 的参与情况，但我只想要每部电影中出场最多的 Actor 。

提前致谢!

Answer 1

解决方案是使用窗口函数(对于 MySQL，这需要 8.0 版本)。

您可以先连接表格，并对每个 Actor 的总出场次数进行窗口计数:

select
    m.id movie_id,
    m.title movie_title, 
    p.id person_id,
    p.name person_name,
    count(*) over(partition by p.id) num_movies
from 
    movie m
    inner join participation pm on pm.id_movie = m.id
    inner join person p on p.id = pm.id_person

然后，您可以按整体外观对每部电影的 Actor 进行排名，并筛选每部电影排名靠前的 Actor :

select *
from (
    select 
        t.*,
        rank() over(partition by movie_id order by num_movies, person_name desc) rn
    from (
        select
            m.id movie_id,
            m.title movie_title, 
            p.id person_id,
            p.name person_name,
            count(*) over(partition by p.id) num_movies
        from 
            movie m
            inner join participation pm on pm.id_movie = m.id
            inner join person p on p.id = pm.id_person
    ) t
) t     
where rn = 1