More JOIN operations/zh

http://sqlzoo.net/wiki/More_JOIN_operations/zh


此教程練習表格合拼。數據庫有三個表格

movie電影(id編號, title電影名稱, yr首影年份, director導演,budget製作費, gross票房收入)

actor演員(id編號, name姓名)

casting角色(movieid電影編號,actorid演員編號, ord角色次序)

角色次序代表第1主角是1, 第2主角是2...如此類推.


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

select movie.id, title

from movie

where yr='1962'


2.   [endif]電影大國民 'Citizen Kane' 的首影年份。

select yr

from movie

where title= 'Citizen Kane'


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

select movie.id,title,yr

from movie

where title like’ Star Trek%’

order by yr


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

select title

from movie

where movie.id in(11768,11955, 21191)


5.   [endif]女演員'Glenn Close'的編號 id是什麼?

select actor.id

from actor

where name= 'Glenn Close’


6.   [endif]電影北非諜影'Casablanca' 的編號 id是什麼?

select movie.id

from movie

where title='Casablanca'


7.   [endif]列出電影北非諜影 'Casablanca'的演員名單。

select name

from actor join casting

on actor.id=casting.actorid

where movieid=11768


8.   [endif]顯示電影異型'Alien' 的演員清單。

select name

from actor join movie on title='Alien' join casting on movie.id=casting.movieid

and actor.id=casting.actorid


9.   [endif]列出演員夏里遜福 'Harrison Ford' 曾演出的電影。

select title

from movie join actor on name= 'Harrison Ford' joincasting on movie.id=casting.movieid and actor.id=casting.actorid


10. [endif]列出演員夏里遜福 'Harrison Ford' 曾演出的電影,但他不是第1主角。

select title

from movie join actor on name= 'Harrison Ford' joincasting on movie.id=casting.movieid and actor.id=casting.actorid and ord!=1


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

select title,name

from movie join casting on yr=1962 and ord=1 join actoron movie.id=casting.movieid and actor.id=casting.actorid


12.尊·特拉华达'John Travolta'最忙是哪一年? 显示年份和该年的电影数目。

movie電影(id,title,yr, director, budget, gross)

actor演員(id, name)

casting角色(movieid,actorid,ord)

movie.id=casting.movieid

actor.id=casting.actorid

· 显示出有'JohnTravolta'出演的电影时间和电影的数量

select yr,count(title) AS C

from movie join actor on name='John Travolta'

join casting on casting.actorid=actor.id andcasting.movieid=movie.id

group by name,yr


· 显示出有'JohnTravolta'出演的电影时间和电影的数量最多的一年。

SELECT MAX(C) FROM

(select yr,count(title) AS C

from movie join actor on name='John Travolta'

join casting on casting.actorid=actor.id andcasting.movieid=movie.id

group by name,yr) as t

· 将语句合并后得出尊·特拉华达'John Travolta'最忙的年份和出演的电影数目。

select yr,count(title) AS C

from movie join actor on name='John Travolta'

join casting on casting.actorid=actor.id andcasting.movieid=movie.id

group by name,yr

having count(title)=( SELECT MAX(C) FROM

(select yr,count(title) AS C

from movie join actor on name='John Travolta'

join casting on casting.actorid=actor.id andcasting.movieid=movie.id

group by name,yr) as t)

或者,根据原题意,需要求出电影数目最大的值和年份。

SELECT yr,COUNT(title) FROM 

  movie JOINcasting ON movie.id=movieid 

         JOINactor   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  ) 


其中显示出出演电影次数最多的语句为↓


SELECT MAX(c) FROM 

(SELECT yr,COUNT(title) AS c FROM 

   movie JOIN casting ONmovie.id=movieid 

         JOIN actor   ON actorid=actor.id 

 WHERE name='John Travolta' 

 GROUP BY yr)as t



13.列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。是否列了電影 "Little Miss Marker"兩次?

 

说明:1、首先求出演員茱莉·安德絲'Julie Andrews'出演过哪些电影(电影id,电影名称)。2、然后求出这些电影的第一主角是谁。

下列语句得出“1、”即茱莉·安德絲出演过哪些电影(以电影id显示。已知演员姓名,可从actor表中得出actor.id,联合表格casting可知movieid,根据movieid=movie.id进一步可知茱莉·安德絲'Julie Andrews'出演过电影名称。)

1、下列语句得出茱莉·安德絲'Julie Andrews'出演过的电影id,即表格casting中的movieid。

Select movieid

From casting join actor on name='Julie Andrews' and actor.id=casting.actorid

2、根据已出演过的电影id求出第一主角和电影名称。此处注意将电影名称去重。采用函数distinct(。)

Select distinct(title),name

From movie join casting on movieid=movie.id and ord=1

Join actor on actorid=actor.id

Where movieid in (Select movieid

From casting join actor on name='Julie Andrews' and actor.id=casting.actorid)

或者

SELECT DISTINCT(title),name FROM movie inner join casting onmovie.id=movieid 

inner join actor on actorid=actor.id and ord=1 

WHERE movieid IN ( 

  SELECT t2.movieid FROM actor t1inner join casting t2 on t2.actorid=t1.id and name='Julie Andrews') 


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

解题:原题要求出曾经30次担任第一主角的演员姓名。(1、先查询出所有担任第一主角的人,即按照姓名分类,依次列出出演过的电影id。2、然后当电影id超过30次即为担任过30次主角,此处是大于等于30次)

Select name

From casting join actor

On actorid=actor.id

Where ord=1

Group by name

Having count(movieid)>=30

Order by name


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

解答:按照电影名称分组列出所有角色id,即字段名actorid(根据表格movie中的title 可以得出movie.id,则在表格casting中可知actorid)再进行计数并排序。

select title,count(actorid) as c

from movie join

casting on movie.id=casting.movieid

where yr=1978

group by title

order by c desc


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

·1、求出演員亞特·葛芬柯'Art Garfunkel'出演过的所有电影(电影id)

Selectmovieid from actor

Joincasting on actor.id=casting.actorid

Wherename='Art Garfunkel'

2、在这些电影id中查询出所有演员姓名,此处注意排除亞特·葛芬柯'Art Garfunkel'自己的名字,采用不等于公式“!= ”。

selectname from actor

Joincasting on actor.id=casting.actorid

Wheremovieid in(Select movieid from actor

Joincasting on actor.id=casting.actorid

Wherename='Art Garfunkel')

Andname!= 'Art Garfunkel'

推荐阅读更多精彩内容