sql - 如何列出所有与 'Art Garfunkel' 合作过的人？

Question

我有 3 个表 actor 、 movie 、 casting 。我必须找到与 Actor “ART Garfunkel”合作过的人。

我有解决方案，但我无法理解以下查询的工作原理。

**movie**   **actor**    **casting**
   id          id           movieid
   title       name         actorid
   yr                       ord
   director     
   budget       
   gross        

SELECT a.name
  FROM (SELECT movie.*
          FROM movie
          JOIN casting
            ON casting.movieid = movie.id
          JOIN actor
            ON actor.id = casting.actorid
         WHERE actor.name = 'Art Garfunkel') AS m
  JOIN (SELECT actor.*, casting.movieid
          FROM actor
          JOIN casting
            ON casting.actorid = actor.id
         WHERE actor.name != 'Art Garfunkel') as a
    ON m.id = a.movieid;

另一种解决方案:

  SELECT actor.name  FROM casting
       JOIN movie ON movie.id=casting.movieid
      JOIN actor ON  actor.id=casting.actorid
     WHERE actor.name !='Art Garfunkel'
    AND
       movie.id IN(SELECT movie.id FROM casting
           JOIN movie ON movie.id=casting.movieid
       JOIN actor ON  actor.id=casting.actorid
        WHERE actor.name='Art Garfunkel')

Answer 1

简单的解决方案:

SELECT distinct actor.name
FROM movie
JOIN casting
ON casting.movieid = movie.id
JOIN actor
ON actor.id = casting.actorid
where movie.id in (select movieid from casting join actor on id =actorid where 
actor.name = 'Art Garfunkel') and actor.name <> 'Art Garfunkel'