SQLZOO MORE JOIN参考答案（含表格样式和解题思路）

时间：2024-10-14 07:58:16

通过这段时间做SQL 的题目，我发现解题思路难点主要有三个：
1：逆向思考，需要你从想要的答案逆向思考到如何从很多表中以怎样的方式取出。
2：所以程序是从内至外而写。
3：正确的找到每个表连接的主键，以合理的顺序应用JOIN和GROUP BY
在这里插入图片描述
这次是针对SQLZOO中多表查询所写的练习题。

我是从第六题开始写，前5题省略。

  1. 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)

思路：
1: 从 casting 表中 找到 movieid 对应的 actorid
2: 从actor 表中 找到 actorid 对应的 name

SELECT ac.name
FROM actor AS ac inner join casting AS ca
ON ac.id=ca.actorid
AND ca.movieid=11768
  • 1
  • 2
  • 3
  • 4

这种由于连接的表不多，我们也可以用子查询的方法：

SELECT name 
FROM actor 
WHERE id in 
(SELECT actorid
FROM casting 
WHERE movieid = 11768)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

可以注意到虽然列的名字不一样(actorid & id)但是 子查询的时候他也可以进行自动匹配

  1. Alien cast list

Obtain the cast list for the film ‘Alien’

思路：
1：从 movie 表中 找到 ‘Alien’ 对应的 id
2: 从 casting 表中 找到 movieid(1的 id) 对应的 actorid
3: 从actor 表中 找到 actorid 对应的 name

Select a.name from actor as a inner join (
Select c.actorid
From movie as m inner join casting as c
On m.id=c.movieid
And m.title Like 'Alien') as tmp
On tmp.actorid=a.id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  1. Harrison Ford movies

List the films in which ‘Harrison Ford’ has appeared

思路：
1：从 actor 表中 找到’Harrison Ford’ 对应的 id
2: 从 casting 表中 找到 actorid(1的 id) 对应的 movieid
3: 从 movie 表中 找到 id(2的movieid) 对应的 title

混淆点：
1：在SELECT 后面要知道自己提出来的是movieid 还是 actorid。


SELECT m.title
FROM movie m INNER JOIN
(SELECT c.movieid 
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
AND a.name LIKE "Harrison Ford")tmp
ON tmp.movieid=m.id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  1. 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]

思路：
1：再上一题目的基础上还要加一个ord!=1 的过滤

SELECT m.title
FROM movie m INNER JOIN
(SELECT c.movieid 
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
AND a.name LIKE "Harrison Ford"
AND c.ord!=1)tmp
ON tmp.movieid=m.id
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  1. Lead actors in 1962 movies

List the films together with the leading star for all 1962 films.

思路：
1.这个就在一个表上的简单的条件查询，答案如下 。

SELECT title ,directore
FROM movie
WHERE yr=1962
  • 1
  • 2
  • 3

Harder Questions

  1. Busy years for Rock Hudson

Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr
FROM movie m INNER JOIN
(SELECT a.name,b.movieid
FROM actor a INNER JOIN casting c
ON a.id=c.actorid
WHERE a.name LIKE 'Rock Hudson' ) tmp
ON m.id=tmp.movieid
GROUP BY yr
HAVING count(title)>2
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

简单一点的写法也可以先通过JOIN 建立一张大表，之后直接在里面对你想要的条件进行搜索。

SELECT yr,count(title)
FROM movie m JOIN casting c
ON m.id=c.movieid
JOIN actor a 
ON a.id=c.actorid
WHERE a.name LIKE 'Rock Hudson'
GROUP BY yr
HAVING count(title)>2
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  1. Lead actor in Julie Andrews movies

List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.

#选出film title & leading actor
SELECT distinct(title)，name
FROM movie m JOIN casting c
ON m.id=c.movieid
JOIN actor a 
ON a.id=c.actorid
WHERE c.ord=1
AND movieid IN
#他参演的所有电影的Movieid
(SELECT movieid
FROM casting c JOIN actor a
ON c.actorid=a.id
AND a.name='Julie Andrews')
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  1. Actors with 15 leading roles

Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

SELECT name 
FROM actor a 
JOIN casting c 
ON c.actorid = a.id 
WHERE c.ord=1 
GROUP BY a.name 
HAVING COUNT(movieid)>=15
ORDER BY name
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  1. List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

考点：

  1. JOIN
  2. GROUP BY
  3. order by

思路：
1：需要title & # of actor 说明需要从movie 和 casting 2个表中取出（JOIN）
2：# of actor需要分组求和之后求出（GROUP BY）

注意由于GB的特殊性，他的SELECT后面所能列出的列名有限，所以我倾向先将我们需要的所有信息尽可能用JOIN列在一张大表里，在用GB进行分组。

3： 最后需要对NUM进行降序排列（desc）

SELECT m.title, COUNT(actorid) 
FROM movie m JOIN casting c
ON m.id=c.movieid
WHERE m.yr = 1978
GROUP BY title
ORDER BY COUNT(actorid) DESC,title
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  1. List all the people who have worked with ‘Art Garfunkel’.

考点：
存在类问题（很像Harder里的第2题，应用到日常工作就是选取特定的某人参与的项目，购买的商品等）
我们用子查询的方法获得需要的数据，
再和另外的条件合在一起进行拼接

思路：

  1. 首先找到有他参加的movieid
  2. 最后把这些movieid对应的actorid找到再连接到对应的name
#将所有这些找到的数据列出，用!=将本人排除
SELECT a.name
FROM actor a 
JOIN casting c
on a.id=c.actorid
WHERE a.name!='Art Garfunkel'
#找到他所参演的movieid
AND c.movieid IN
( SELECT m.id
FROM movie m
JOIN casting c 
ON m.id=c.movieid
JOIN actor a
ON a.id=c.actorid
WHERE a.name='Art Garfunkel')
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

相关文章