sql - 如何使用group by编写sql查询

Question

我有这张表:

actors(id: int, first_name: string, last_name: string, gender: string)
directors(id: int, first_name: string, last_name: string)
directors genres(director id: int, genre: string, prob: 
float)
movies(id: int, name: string, years: int, rank: 
float,)
movies directors(director id: int, movie id: int)
movies genres(movie id: int, genre: string)
roles(actor id: int, movie id: int, role: string)

我想找出每种类型的电影制作数量最多的年份。

我正在执行以下操作，但卡住了，请帮忙!

select m.YEAR, count(m.year) as c, genre 
from movies_genres, 
     movies m 
where m.id = movies_genres.movie_id 
group by genre, m.year;

Answer 1

您可以获得每年每种类型的电影数量，这很棒。现在，您只需通过将查询作为派生表来选择其中的最大值。

select genre, year, max(c) mc
from
    (select m.YEAR, count(m.year) as c, genre 
    from movies_genres mg
         inner join movies m
             on m.id = mg.movie_id 
    group by genre, m.year)
group by genre, year