8.SQL演習(3/3)

6-1　where の条件は teamid = 'GER'

SELECT ????????????? FROM goal 

  WHERE teamid = 'GER'

6-2

SELECT id,stadium,team1,team2

  FROM game 

 WHERE ????????????????

6-3　方法１

SELECT ????????????????

  FROM game JOIN goal ON (id=matchid and ??????)

　方法２

SELECT ????????????????

  FROM game JOIN goal ON (id=matchid)

  where ??????

6-4　3の問題が解けたらコピーして条件の部分を修正

6-8　例としてドイツ―ギリシャ戦の全試合を表示するクエリーが入力してある。

6-9　select の項目で count(*)を使用し、 GROUP BY teamname を利用する。

6-13

この問題は、ここまでまだ未解説のSQL構文「CASE WHEN」 を使用する。

下に示す様に、各試合ごとに各チームの得点を表示する。

mdate team1 score1 team2 score2 日程 チーム名１ 得点１ チーム名２ 得点２

注意） 全得点状況が、記録されている。もし、チーム名が goal に記録されていれば、

その時点でチームが１得点していることにななり、チーム名が記載されていなければ、得点は０点である。

チーム名有り → １

チーム名なし → ０

この、得点状況を １と０ に CASE WHEN で変換した結果をSUM で集計すれば、そのチームの得点を集計できる。

結果は、日程順で並べ替えて出力する(日程が同じなら、idの順番)。

※難しいので長文で解説

例文のＳＱＬ：

SELECT mdate,  team1,

  CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1

  FROM game JOIN goal ON matchid = id

ＳＱＬの解説

SELECT mdate,  game.team1, goal.teamid

  FROM game JOIN goal ON goal.matchid = game.id

で game と goal が結合できる。

この結合方法では team1 が参加した試合について、自国の得点と対戦国の得点の両方が出力される。

そこで出力結果が自国の場合は １点、 他国の場合は ０点に 出力を変換して表示する。

その為に以下の様に selectの表示項目で、CASE WHEN を利用する。

    CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1

上記の条件部分で、  goal.teamid が  game.team1 に一致するかを判定して、  THEN 1 で一致の場合は １ に ELSE 0 で不一致の場合は 0 に変換している。

ENDの後ろの score1 で、出力結果の見出しに score1 という名前を付けている。（AS score1 の省略形）

(ヒント)

自国の得点状況を集計するには、

SELECT mdate,

  team1,

  sum(

  CASE WHEN teamid=team1 THEN 1 ELSE 0 END

  )  as score1

 FROM game

 LEFT JOIN goal ON goal.matchid = game.id

 GROUP BY game.id,mdate,team1,team2

これに、team2 の国名と 得点の集計結果を同様に付け加える。

JOINではなく、 LEFT JOIN を使用する必要が有る。試合で両国とも無得点 0-0 の場合は、 goalには、記録がない。

eteamにチーム名が記載されていても、goalにゴールの記録がないチームは、JOIN でテーブルを結合すると、結合結果には残らない（inner join 内部結合のため）。

そのためLEFT JOINで結合して得点の欄は空欄(NULL)となるように結合する。

sumで集計した際に得点の無い試合の集計結果は 0点になる。

集計結果は最終的に日程順に並べ替える。 order by を使う。

7-1. 　 実行するだけ。文法の確認。

7-2. 　 1. を修正。 文字列の表記は ' と'で囲む必要に注意。

7-3. 　Star Trek という語句を含む映画を where の条件に指定。

7-4. 　select で表示して調べる

7-5. 

Get to the point（要点） のリンク先にテーブルの結合のSQLの例が掲載されているので確認。

↑に２つのテーブルの結合例（casting と actor） と ３つの全テーブルの結合例が載っているので、回答に利用するとよい。

7-6. 　上の問題で、カサブランカの id が分かっているので、単純なクエリーで回答してもよい。 casting と actor を結合 where で カサブランカの id を指定。

（別解１） カサブランカの id を調べるサブクエリーを利用する。

（別解２）全テーブルを結合してから、条件指定で検索結果を絞り込む。

7-7. 　7-6の別解を修正して回答。

7-8.  　全テーブルを結合し、条件指定。

7-9.   　ord は、映画の出演リスト順、1 が主演を意味する。 ord<>1 で 出演順が1ではないという条件になる。

7-11. 　実行するだけで正解が出てくる。SQLを解釈して、なぜそのような結果になるか考えること。

サブクエリの部分について解説。

(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

)

上記クエリの解釈。

全テーブルを内部結合。

 WHERE name='John Travolta' ジョン＝トラボルタのレコードだけを抽出

group by yr で年ごとにグループ化し COUNT(title) AS c で、出演したタイトルの本数を数え、項目名を c とする。

外側の select 文で、 c の欄の最大値を求めている。

※ 末尾の AS t はサブクエリーの実行結果は複数値を含みテーブルとして利用するので、仮の名前 t を指定している。名前が必要となるのは mysqlの仕様のため。

上記クエリをサブクエリとして、 7-12 の HAVING の条件に使用する。

7-12. 　入力済みのSQLをまず実行してみる。'Julie Andrews'  が出演した映画のidのリストを得ることができるので、これをサブクエリーとして、さらに外側のselect文（全テーブルを結合）を追加して組み合わせて回答する。

7-13. 　actor.id でグループ化

7-14. 　movie.id でグループ化

7-15. 　一緒に出演したか彼が監督した映画に出演した という条件で検索する。役者以外に監督の名前とid もactor テーブルに記録されている。