sqlzoo - 7 More JOIN operations

- 代表省略 与前面题型相同
* 代表错题，重点看
? 代表有疑问

---

6. ? Cast list for Casablanca

Obtain the cast list for ‘Casablanca’.
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)

获取Casablanca电影中所有的演员名

通过Casablanca电影名获取唯一的电影ID, 通过电影ID获取多个参演的演员ID, 通过每个演员ID获取对应的演员名 (电影名和电影ID一一对应, 演员名和演员ID一一对应)

• 方法1: 使用内部联结 + 子查询

select name from actor 
join casting on actor.id = actorid 
where movieid in (select id from movie where title = 'Casablanca')

join on 内部联结后接where子句, where后movieid in / moveid =都可以

• 方法2:使用子查询

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

select 嵌套 这里select id from movie where title = 'Casablanca'返回唯一值11768, 前面使用movieid = 答案正确, 使用movieid in 答案错误? 但使用 movieid in ('11768') 答案正确, 不知道原因???

• 方法3: 联结多个表

select name from actor join casting on actor.id = actorid 
join movie on movieid = movie.id and title = 'Casablanca'

join on 联结可以多次使用, and title = 'Casablanca' 条件可以用 and 连接, 也可以用where子句

select name from actor, casting, movie 
where actor.id = actorid and movieid = movie.id 
and title = 'Casablanca'

与上面方式等效

7. - Alien cast list

与6同, 几种方式都可以实现

8. - Harrison Ford movies

与6同, 几种方式都可以实现

9. Harrison Ford as a supporting actor

List the films where ‘Harrison Ford’ has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]


列出 ‘Harrison Ford’ 参演但不是主演的电影

由于有两个条件在不同的表中，采用联结多个表的方式筛选条件
1、演员名为 ‘Harrison Ford’ （在表actor 中）
2、不是主演（ord<>1 在表 casting 中）

-- 方法1 join on 
select title from movie 
join casting on movie.id = movieid 
join actor