sqlzoo第七关More JOIN operations

第七关 More JOIN operations

• 电影数据库

表1 电影表（movie）

id(编号)	title（电影名）	yr(年份)	director(导演)	budget(制作费)	gross（票房收入）
10003	“Crocodile” Dundee II	1988	38	15800000	239606210
10004	Til There Was You	1997	49	10000000
…	…	…	…	…	…

表2 演员表（actor）

id(编号)	名字（name）
20	Paul Hogan
50	Jeanne Tripplehorn

表3 角色表(casting)

movieid（电影编号）	actorid(演员编号)	ord（角色排序）
10003	20	4
10004	50	1

7.1题目及答案

1. 列出1962年首影的電影， [顯示 id, title]

SELECT id,title FROM movie  WHERE yr=1962 

2. 電影大國民 ‘Citizen Kane’ 的首影年份。

select yr from movie where title = 'Citizen Kane'

3. 列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id,title,yr from movie where title like 'Star Trek%' order by yr

4. id是 11768, 11955, 21191 的電影是什麼名稱?

select title from movie where id in (11768,11955,21191)

5. 女演員’Glenn Close’的編號 id是什麼?

select id from actor where name = 'Glenn Close'

6. 電影北非諜影’Casablanca’ 的編號 id是什麼?

select id from movie where title = 'Casablanca'

7. 列出電影北非諜影 'Casablanca’的演員名單。
   什麼是演員名單?
   演員名單,即是電影中各演員的真實姓名清單。
   使用 movieid=11768, 這是你上一題得到的結果。

解法一：子查询的方式

select name from actor 
	where id in (select actorid from casting where movieid = 11768)

解法二：

select name from actor 
	join casting on (id = actorid) 
	where movieid = 11768

8. 顯示電影異型’Alien’ 的演員清單。

解法一：子查询的方式（好理解，但不推荐）

select name from actor 
	where id in (select actorid from casting where movieid 
	= (select id from movie where title = 'Alien'))

解法二：

select name from casting
	join actor a  on (a.id = actorid)
	join movie m on (m.id = movieid)  
	where title = 'Alien'					//两表联查

9. 列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

解法一：子查询的方式（好理解，但不推荐）

select title from movie
	where id in (select movieid from casting 
		join actor on id = actorid and name = 'Harrison Ford')

解法二：

select title from casting
	join actor a on(a.id = actorid)
	join  movie m on(m.id = movieid) 
	where  name = 'Harrison Ford'

10. 列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

解法一：子查询的方式（好理解，但不推荐）

select title from movie where id 
	in (select movieid from casting 
	join actor on id = actorid and name = 'Harrison Ford' and ord != 1)

解法二：

select title from casting 
	join movie m on (m.id = movieid)
	join actor a on (a.id = actorid) 
	where  name = 'Harrison Ford' and ord != 1

11. 列出1962年首影的電影及它的第1主角。

select title,name from casting 
	join movie m on (m.id = movieid)
	join actor a on (a.id = actorid)
	where yr = 1962 and odr = 1

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    	//having追加条件，取电影次数最大值
(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
)

解法二：

SELECT yr,COUNT(title) c FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr 								//获取每年电影次数
order by c desc limit 1						//按次数逆序排序，取第一个

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

select title,name from casting 
	join movie m on (m.id = movieid)
	join actor a on (a.id = actorid)
where  ord = 1 and movieid in(
	SELECT movieid FROM casting
	join actor a on(a.id = actorid)
	WHERE name='Julie Andrews') 

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

select distinct name from casting 
join movie m on (m.id = movieid)
join actor a on (a.id = actorid)
where actorid in (
	select actorid from casting where ord = 1 
	group by actorid 
	having count(actorid)>=30)
order by name

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

select title,count(title) as c from casting 
join movie m on (m.id = movieid)
where movieid in 
	(select id from movie where yr = 1978) 	
group by title 
order by c desc

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

select distinct name from actor 
join casting on (id = actorid) 
where name != 'Art Garfunkel' and movieid in
	(select movieid from casting  join actor on(id = actorid) 
	where name = 'Art Garfunkel')

7.2 总结

本章节只要是多表关联查询和子查询结合使用，基本方法：

1. 查询时先看要显示的字段属于哪几个表。
2. 了解这几张表的关系，并通过join将表关联。
3. 添加条件，where，group by，having，order等。
4. 条件的取值可以是固定值，也可以是子查询的结果。

• join后面可以跟表，也可以跟子查询的临时数据表