我想找到每一种类型的电影,找到N个在大多数类型的电影中扮演角色的演员
我已经这样做了:
select genre.genre_name,actor.actor_id,count(genre.genre_name) from genre
inner join movie_has_genre on movie_has_genre.genre_id=genre.genre_id
inner join movie on movie_has_genre.movie_id=movie.movie_id
inner join role on movie.movie_id=role.movie_id
inner join actor on actor.actor_id=role.actor_id
group by genre.genre_name,actor.actor_id;
这给出了每种类型每个演员演过多少部这种类型的电影,现在我想找出每个类型中演过最多电影的演员。
Tables and their columns:
actor(actor_id,name)
role(actor_id,movie_id)
movie(movie_id,title)
movie_has_genre(movie_id,genre_id)
genre(genre_id,genre_name)
同样,结果应该是这样的:
Action 22591 7
Horror 25863 3
Horror 24867 3
Comedy 23476 2
Drama 14536 1
Drama 19634 1
Drama 17563 1
发布于 2018-05-29 07:25:41
伙计,我要做的是下一步(假设你的代码运行良好):
-- Notice this is your code with some aliases, nothing else.
-- Just for making mi job easier.
create view frequency as
select genre.genre_name as genre_ name,
actor.actor_id as actor_id,
count(genre.genre_name) as freq
from genre
inner join movie_has_genre on movie_has_genre.genre_id=genre.genre_id
inner join movie on movie_has_genre.movie_id=movie.movie_id
inner join role on movie.movie_id=role.movie_id
inner join actor on actor.actor_id=role.actor_id
group by genre.genre_name,actor.actor_id;
-- And this is my proposal
-- Take the max frequency per each category
-- and find the guy who possesses it (maybe 2 or more...)
select genre.genre_name,actor.actor_id
from frequency as tbl1 inner join
(
-- The max frequency in a genre.
select f.genre_name,
max(f.freq) as max_freq
from frequency f
group by(genre_name)
) as tbl2 on (tbl1.genre_name = tbl2.genre_name)
where tbl1.freq = tbl2.max_freq;
但是,有一个问题:如果存在平局,它可能会为每个类别返回多个演员。但是我怎么知道谁是赢家呢?我把它给你了。也许这是错的,我不这么认为,但我们都在学习!希望能帮到你。
发布于 2018-05-29 08:06:30
您需要使用MAX()
函数。一些SQL实现(比如Oracle)允许您这样做:SELECT MAX(COUNT(whatever))
,但MySQL不在其中。
做你想做的事情的一种方法是:
select genre_name, actor_id, max(genrecount)
from (
select genre.genre_name, actor.actor_id, count(genre.genre_name) as genrecount
from genre
inner join movie_has_genre on movie_has_genre.genre_id=genre.genre_id
inner join movie on movie_has_genre.movie_id=movie.movie_id
inner join role on movie.movie_id=role.movie_id
group by genre.genre_name,actor.actor_id
) as topactor
这将在从内部SELECT
派生的表上执行外部SELECT
。
https://stackoverflow.com/questions/50574260
复制相似问题