SQL最常见

Question

我想知道，哪些演员主要演喜剧电影。他们大部分的电影记录都是喜剧。

所以，当一个演员有7部电影，其中4部是喜剧。

(也包括所有只演过喜剧的演员。)

表“演员”：

|id_actor| name |
|--------|------|
|  12    |franco|
|  13    |rogen |
|  14    |hill  |
|  15    |teller|
|  16    |john  |

表“体裁”

|id_genre|    name    |
|--------|------------|
|  22    |comedy      |
|  23    |thriller    |
|  24    |documentary |

表"plays_in"：

|id_actor|    id_film |
|--------|------------|
|  12    |      5001  |
|  12    |      5002  |
|  12    |      5003  |
|  13    |      5004  |
|  13    |      5005  |
|  13    |      5006  |
|  14    |      5007  |
|  14    |      5008  |

表is_in_genre

|id_genre|    id_film |
|--------|------------|
|  22    |      5001  |
|  22    |      5002  |
|  22    |      5003  |
|  23    |      5004  |
|  23    |      5005  |
|  24    |      5006  |
|  24    |      5007  |
|  24    |      5008  |

这就是我所拥有的，但它不给演员看，只演喜剧。

select      id_actor, name 
from        actor x where
(
    select      count(id_film) 
    from        actor y
    natural join plays_in 
    natural join movie
    natural join is_in_genre
    where       id_genre = 4001 and x.id_actor = y.id_actor 
    group by    id_actor
)
<=
(
    select      count(id_film) 
    from        actor y
    natural join plays_in 
    natural join movie
    natural join is_in_genre
    where       id_genre != 4001 and x.id_actor = y.id_actor 
    group by    id_actor
)

Answer 1

加入所有4个表和每个参与者组。

只有只演过50%以上喜剧的演员，分组后的条件是：

having (1.0 * sum(case when genrename = 'comedy' then 1 else 0 end ) / count(genrename)) > 0.5。

select 
  t.actorname
from (
select 
  actor.name actorname, genre.name genrename
from actor 
inner join plays_in
on plays_in.id_actor = actor.id_actor
inner join is_in_genre
on is_in_genre.id_film = plays_in.id_film
inner join genre
on genre.id_genre = is_in_genre.id_genre
) t
group by t.actorname
having (1.0 * sum(case when genrename = 'comedy' then 1 else 0 end ) / count(genrename)) > 0.5

见演示

Answer 2

当电影的一种类型是喜剧时，它就是一部喜剧。你要找的演员是那些电影主要是喜剧的演员。

with comedies as
(
  select id_film
  from is_in_genre
  group by id_film
  having count(case when id_genre = (select id_genre from genre where name = 'comedy') then 1 end) > 0
)
, comedy_actors as 
(
  select id_actor
  from plays_in
  group by id_actor
  having avg(case when id_film in (select id_film from comedies) then 1 else 0 end) > 0.5
)
select *
from actor
where id_actor in (select id_actor from comedy_actors);