More JOIN operations/ja
| Language: | English • 日本語 • 中文 |
|---|
このチュートリアルで join の理解を深める。データベースを3つのテーブルで構成する。 movie（映画） と actor（役者） と casting（出演） である。
テーブルの詳細は以下である。
| id | title タイトル | yr 年 | director 監督 | budget 予算 | gross 収益 |
| id | name 名前 |
| movieid | actorid | ord 出演掲載順序 1なら主演 |
※ id は movie.id と actor.id の2種類あるので注意。SQLではテーブル名(movieとactor)とフィールド名を . で接続して区別する。
※ movie.id = actor.id にはならない（映画のid と 役者のid なので 参照キーと外部キーの関係にはならない）
※ movie.id = casting.movieid や actor.id = casting.actorid の関係がテーブル間にある。
映画データベースの詳細は以下のリンクから表示。 More details about the database.
1962の映画
1962 年の映画のリストを表示（ id と title を表示）
（ヒント） 実行するだけ。文法の確認。
SELECT id, title
FROM movie
WHERE yr=1962
SELECT id, title
FROM movie
WHERE yr=1962
「市民ケーン」の上映年は？
'Citizen Kane'の年を示す。
SELECT yr
FROM movie
WHERE title='Citizen Kane'
スタートレック映画
スタートレック（'Star Trek'）というシリーズ映画のリストを表示（ id title yr ）。 年の順に掲載。
SELECT id,title, yr FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr
グレン・クローズのid
女優 'Glenn Close' の id ナンバーは何ですか？
SELECT id FROM actor
WHERE name= 'Glenn Close'
カサブランカのid
映画 'Casablanca' カサブランカの id は何ですか？
SELECT id
FROM movie
WHERE title='Casablanca'
カサブランカの出演者リスト
映画カサブランカの出演リスト(name)を出力する。'Casablanca'
その映画に出演した役者のリスト
movieid=11768※を使う。 (または、これまでの問題で得た値を何でも使ってよい)
※訳者注 データベースエンジンがMySQLに設定されている場合の値。
SELECT name
FROM casting, actor
WHERE movieid=(SELECT id
FROM movie
WHERE title='Casablanca')
AND actorid=actor.id
エイリアンの出演リスト
映画「エイリアン」'Alien'の出演者リストを表示。
SELECT name
FROM movie, casting, actor
WHERE title='Alien'
AND movieid=movie.id
AND actorid=actor.id
ハリソン＝フォード映画
'Harrison Ford' ハリソン＝フォードが出演した映画のリストを表示する。
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
ハリソン＝フォード共演
'Harrison Ford'ハリソン＝フォードが出演した映画で、彼が主演していない(ord <> 1） のリストを表示。
[Note: ord は、映画の出演リスト順、1 が主演を意味する。]
SELECT title
FROM movie, casting, actor
WHERE name='Harrison Ford'
AND movieid=movie.id
AND actorid=actor.id
AND ord<>1
1962映画の主演者
1962年の全映画を、そのタイトルと主演と併記してリスト表示。
SELECT title, name
FROM movie, casting, actor
WHERE yr=1962
AND movieid=movie.id
AND actorid=actor.id
AND ord=1
より難しい問題
ジョン＝トラボルタが多忙の年
'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
(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) 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
(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
)
ジュリー＝アンドリューズ出演映画
'Julie Andrews' ジュリー＝アンドリューズが出演した映画について、主演した役者の名前を調べ、その全てについて タイトル と 主演 を表示する。
Julie Andrews は Little Miss Marker の1980 年のリメイクに主演したが、オリジナル(1934)ではしていない。
Title はユニークフィールドではない。サブクエリーでidのテーブルを作成する。
SELECT movieid FROM casting
WHERE actorid IN (
SELECT id FROM actor
WHERE name='Julie Andrews')
SELECT title, name
FROM movie, casting, actor
WHERE movieid=movie.id
AND actorid=actor.id
AND ord=1
AND movieid IN
(SELECT movieid FROM casting, actor
WHERE actorid=actor.id
AND name='Julie Andrews')
主演30本の役者達
少なくとも３０タイトル以上に主演した役者の名前をアルファベット順に掲載。
SELECT name
FROM casting JOIN actor
ON actorid = actor.id
WHERE ord=1
GROUP BY name
HAVING COUNT(movieid)>=30
1978年の映画
1978年の映画を、出演者数が多い順に、タイトルを表示。
SELECT title, COUNT(actorid)
FROM casting,movie
WHERE yr=1978
AND movieid=movie.id
GROUP BY title
ORDER BY 2 DESC,1 ASC
アート＝ガーファンクルと一緒に
'Art Garfunkel' アート＝ガーファンクルと一緒に仕事をした人々をすべて表示。
SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
JOIN casting b on (a.movieid=b.movieid)
JOIN actor c on (b.actorid=c.id
and c.name='Art Garfunkel')
WHERE d.id!=c.id
Using_Null/ja これで確実に十分。外部結合(OUTER JOIN)に関する次のチュートリアルを見る状況ですよね。
DataWars: Practice Data Science/Analysis with +100 Real Life Projects