SQLZOO: More JOIN operations/zh--用子查询创建一个临时表时要记得给这个表命名

12. 尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

注意最后的AS t，没有这个是不行的

13. 列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。

select title,name
from movie JOIN casting ON movie.id=movieid
           JOIN actor   ON actorid=actor.id
where ord = 1 and movieid in 
(select movie.id from
 movie JOIN casting ON movie.id=movieid
       JOIN actor   ON actorid=actor.id 
and name = 'Julie Andrews')

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

select name
from casting JOIN actor ON actorid=actor.id
where ord = 1
group by name
having count(movieid)>=30
order by name

15. 列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

select title,count(actorid) as a
from movie JOIN casting ON movie.id=movieid
where yr=1978
group by title
order by a desc

16. 列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。

select name
from casting JOIN actor ON actorid=actor.id
where name !='Art Garfunkel'
and movieid in (select movieid 
from casting JOIN actor ON actorid=actor.id
where name = 'Art Garfunkel')