sqlzoo-more join

1-11题参考链接
10.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

SELECT title
      FROM casting a 
          JOIN movie b ON(a.movieid=b.id)
          JOIN actor c ON(a.actorid=c.id)
              WHERE name='Harrison Ford' and ord!=1

13.List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
这道题与第10题的区别在于，子查询中id要表明是哪个id。另外，此题要列出演员参演过的电影名称，在同一年可能参演过多次同一部电影，所以电影名称并不严谨，要用电影id做子查询筛选条件

select title, name
from movie m
join casting c on m.id = c.movieid
join actor a on a.id = c.actorid
WHERE movieid in (
      SELECT movieid FROM casting 
           WHERE actorid in (
                 SELECT id FROM actor WHERE name='Julie Andrews'
)
             
)
and ord =1

14.按字母順序，列出哪一演員曾至少作30次第1主角。

SELECT name FROM actor a JOIN casting c ON(a.id= c.actorid)
            WHERE ord=1
                 GROUP BY name
                     HAVING COUNT(*)>=30 
                         ORDER BY name

15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。
这个题目系统会报错，我看了很多博主都是这样的问题，语句应该是没有问题，待解答，如果有同学知道为什么，请留言，谢谢

SELECT distinct title,count(actorid) 
FROM movie m
JOIN casting c ON(c.movieid=m.id)
WHERE yr=1978 group by title
order by count(actorid) desc

16.列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名
思路：筛选条件为A参演过的所有电影的且不包含A

SELECT name FROM casting JOIN actor ON actorid=id 
     WHERE name !='Art Garfunkel' and movieid IN
      (SELECT movieid FROM casting join actor ON actorid=id WHERE name='Art Garfunkel')