sqlzoo 7.More JOIN operations 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/More_JOIN_operations#1962_movies

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

7 More JOIN operations

7.1 1962 movies

SELECT id, title
 FROM movie
 WHERE yr=1962

7.2 When was Citizen Kane released?

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

7.3 Star Trek movies

SELECT id, title, yr
FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr

7.4 id for actor Glenn Close

SELECT id
FROM actor
WHERE name = 'Glenn Close'

7.5 id for Casablanca

SELECT id
FROM movie
WHERE title = 'Casablanca'

7.6 Cast list for Casablanca

找出电影的id$\Rightarrow$列出演员的名字

SELECT actor.name
FROM actor LEFT JOIN casting ON actor.id = casting.actorid
WHERE casting.movieid = 
(
   SELECT id
   FROM movie
   WHERE title = 'Casablanca'
)

7.7 Alien cast list

找出出演的电影id$\Rightarrow$找出对应id电影的演员名字

SELECT actor.name
FROM actor LEFT JOIN casting ON actor.id = casting.actorid
WHERE casting.movieid = 
(
   SELECT id
   FROM movie
   WHERE title = 'Alien'
)

7.8 Harrison Ford movies

找出相应的电影id$\Rightarrow$找出相应id的名字

SELECT title
FROM movie
WHERE id IN
(
  SELECT casting.movieid
  FROM actor LEFT JOIN casting ON actor.id = casting.actorid
  WHERE actor.name = 'Harrison Ford'
)

7.9 Harrison Ford as a supporting actor

找出相应的电影id$\Rightarrow$找出相应id的名字

SELECT title
FROM movie
WHERE id IN
(
  SELECT casting.movieid
  FROM actor LEFT JOIN casting ON actor.id = casting.actorid
  WHERE actor.name = 'Harrison Ford' AND casting.ord <> 1
)

7.10 Lead actors in 1962 movies

SELECT movie.title, actor.name
FROM 
actor INNER JOIN casting ON actor.id = casting.actorid
INNER JOIN movie ON movie.id = casting.movieid
WHERE yr = 1962 AND casting.ord = 1 

7.11 Busy years for Rock Hudson

COUNT,GROUP BY应用

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE actor.name = 'Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

7.12 Lead actor in Julie Andrews movies

找出Julie出演的电影$\Rightarrow$找电影名称及对应主要演员

SELECT movie.title, actor.name
FROM 
actor INNER JOIN casting ON actor.id = casting.actorid
INNER JOIN movie ON movie.id = casting.movieid
WHERE casting.ord = 1 AND casting.movieid IN (
  SELECT DISTINCT casting.movieid
  FROM casting INNER JOIN actor ON actor.id = casting.actorid
  WHERE actor.name = 'Julie Andrews'
)

7.13 Actors with 15 leading roles

题目：按字典序排序，输出担任至少15次主要角色的演员

题解：有点意思，咋一看需要的信息还是挺多的。得把各种信息拆分以下。

找出每个演员担任过多少次主要角色，此部分在casting表中用SUM+CASE WHEN可计算出

找出15次，就在外面再包一层，找出所需演员的id。

最后再在actor表中查询这些演员的名字即可。

SELECT name
FROM actor
WHERE id IN 
(
-- 至少15次主要角色演员的id
SELECT actorid
FROM
(
  -- 担任多少次主要角色
  SELECT actorid, SUM(CASE ord WHEN 1 THEN 1 ELSE 0 END) AS ss
  FROM casting
  GROUP BY actorid
) AS temp1
WHERE ss >= 15
) 
ORDER BY name

7.14 (no name)

题意有坑，它的按照演员数量排序没有指明倒序，而题目要求倒序。

SELECT movie.title, temp1.ActorNum
FROM movie INNER JOIN 
(
  SELECT movieid, COUNT(*) AS ActorNum
  FROM casting
  GROUP BY movieid
) AS temp1
ON movie.id = temp1.movieid
WHERE movie.yr = 1978
ORDER BY temp1.ActorNum DESC,movie.title

7.15 (no name)

一环套一环

找出Art Garfunkel出演的电影$\Rightarrow$找出在这些电影里出演的演员的id$\Rightarrow$找出这些id演员的名字

SELECT name
FROM actor
WHERE id IN 
(
  SELECT DISTINCT actorid
  FROM casting,
  (
    SELECT id
    FROM actor
    WHERE name = 'Art Garfunkel'
  ) AS temp1
  WHERE actorid <> temp1.id
  AND movieid IN (
    SELECT movieid
    FROM casting
    WHERE actorid = temp1.id
  )
)