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