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

木木mum

于 2018-04-28 21:24:21 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL相关

本文链接：https://blog.youkuaiyun.com/weixin_38393494/article/details/80139462

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
 mov