下面是一个sqlfiddle，它实现了mysql中某一列的按组最大值的算法。

select aActors.genre_id, aActors.actor_name, aActors.aCount as movies_count
from (
  select count(*) as aCount, actor_name, genre_id
  from actors 
  group by actor_name, genre_id
  order by aCount desc
  ) as aActors
left join (
  select count(*) as aCount, actor_name, genre_id
  from actors 
  group by actor_name, genre_id
  order by aCount desc
  ) as bActors
  on aActors.genre_id = bActors.genre_id 
  and aActors.aCount < bActors.aCount
where bActors.genre_id IS NULL;

尝试此查询：

select genre_name, actor_name, cnt from (
    select actor_name,
           genre_name, 
           cnt,
           row_number() over (partition by genre_name order by cnt desc) `rn`
    from (
        select actor_name, genre_name, count(*) `cnt` from actors `a`
        join genres `g` on a.genre_id = g.genre_id
        group by actor_name, genre_name
    ) `a`
) `a` where rn = 1;

笔记
上面的查询使用了mysql 8.0中引入的窗口函数，因此如果您使用的是早期版本，那么这个查询将导致错误。