SQLmore join

碰到了一个让我自己想，会做不起的题
尊·特拉華達’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
)

分析一下：
题目要求找出最忙的那年，所以就是找出某一年中John Travolta所上映的电影最多的年份，并且显示该年的电影数目
第一步：统计出John Travolta有电影上映的年份，并统计上映的电影数：

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

（截图没截完）
从结果看，统计了该演员的电影首映年份，以及概念上映的电影数，记为c
第二步就是选出c中最大的了

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

结果就是选出最大的是几。

最后呢，就是having筛选了
注意：对于join了的表格，如果在sum, count操作以后，还需要筛选，就用having 。