mysql查询以查找在两部电影中扮演的 Actor

Question

我正在尝试展示两部电影中的 Actor 请帮忙，它没有得到

SELECT actors.first_name,actors.last_name
FROM actors
LEFT JOIN films_actors
  ON actors.actor_id=films_actors.actor_id
LEFT JOIN films
  ON films_actors.film_id=films.film_id
WHERE films.title="ACADEMY DINOSAUR"
  AND films.title="ANACONDA CONFESSIONS"

Answer 1

您可以使用 HAVING COUNT(DISTINCT ...)

SELECT actors.first_name,actors.last_name 
FROM actors 
JOIN films_actors 
  ON actors.actor_id=films_actors.actor_id
JOIN films 
  ON films_actors.film_id=films.film_id 
WHERE films.title IN ('ACADEMY DINOSAUR','ANACONDA CONFESSIONS')
GROUP BY actors.first_name,actors.last_name 
HAVING COUNT(DISTINCT films.title) = 2;