SQL计数按组 [英] SQL count by group

问题描述

我必须遵循以下架构

Movie(mvID, title, rating, year)
Director(directorID, firstname, lastname)
Genre(mvID*, genre)
Direct(mvID*, directorID*)

b $ b

我需要知道那些导演喜剧类型的大多数电影的导演，并输出他们的细节，以及他们在那个类型中制作了多少电影。

I need to know the director that directed to most movies of say the comedy genre and output their details with the count of how many movies they made in the that genre.

所以我有

SELECT Director.DirectorID, Director.FirstName, Director.LastName, COUNT(*)
FROM Direct, Genre, Director
WHERE Direct.mvID = Genre.mvID
AND Genre.genre = 'Comedy'
AND Direct.DirectorID = Director.DirectorID
AND COUNT(*) > ALL
GROUP BY Director.DirectorID, Director.FirstName, Director.LastName
ORDER by COUNT(*) DESC

$ b $

推荐答案

select d.firstname,d.lastname,count(g.mvId)
from Director d
inner join Direct dr on d.directorId=dr.directorId
inner join Genre g on dr.mvId=g.mvId
where g.genre='comedy'
group by d.firstname
having count(g.mvId)=max(g.mvId)

这篇关于SQL计数按组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！