sqlzoo第四天

网站：https://sqlzoo.net/wiki/More_JOIN_operations/zh
1.尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。
select yr,count(title)
from actor inner join casting on actor.id=actorid
inner join movie on movieid=movie.id
where name=‘John Travolta’
group by yr
having count(title)=(select max© from(select yr ,count(title) as c
from actor a inner join casting c
on a.id=actorid
inner join movie m
on movieid=m.id
where name=‘John Travolta’
group by yr)as t)

思路：先找出John Travolta在几几年演了多少电影。获取最多电影的数量，通过这个数量来找到哪一年产量最多。需要注意派生表必须有别名也就是这儿的t。