6. JOIN /SQL ZOOの答えと解説

https://sqlzoo.net/wiki/More_JOIN_operations/ja
SQL Zooの回答をまとめました。

1. matchid と player 名をドイツ(Germany)チームの全ゴールについて表示する。

*の使い方

最初の例としてラストネームが Bender である選手のゴール記録が示されている。 「*」でテーブルの全フィールド（カラム）を宣言する。これは matchid, teamid, player, gtime を短く書く方法である。

ドイツプレイヤーを識別するには、次を確認: teamid = ‘GER’

SELECT * FROM goal WHERE player LIKE '%Bender'

回答

Q: matchid と player 名をドイツ(Germany)チームの全ゴールについて表示する。

A :

SELECT matchid, player FROM goal WHERE teamid = 'GER'

2. 試合 1012 の id, stadium, team1, team2 を表示する。

Q:試合 1012 の id, stadium, team1, team2 を表示する。

A:

SELECT id,stadium,team1,team2 FROM game where id = 1012

3. ドイツの全ゴールについて player, teamid ,stadium, mdate を表示するように修正する。

JOINで結びつける

JOIN を利用して、２つのステップを組み合わせて単独のクエリ―にすることができる。 gameテーブルにgoalテーブルをJOIN。 ON以下で結びつける条件（goal.id = game.matchidで一致させている）

SELECT goal.player,goal.teamid,game.stadium, game.mdate FROM game 
JOIN goal ON (game.id=goal.matchid) 
WHERE goal.teamid = 'GER'

4. Marioという名前の選手のゴールについて、team1, team2 , player を表示する。

Select game.team1, game.team2, goal.player from goal 
JOIN game ON game.id = goal.matchid 
WHERE goal.player LIKE '%Mario%'

5. 最初の１０分間でゴールしたという条件で、 player, teamid, coach, gtime を表示。

eteam には各参加国のコーチが記載されている。
JOIN で goal を eteam に結合する。

goal JOIN eteam on teamid=id

SELECT player, teamid, eteam.coach, gtime FROM goal 
JOIN eteam ON eteam.id = goal.teamid 
WHERE gtime<=10

6. team1のコーチcoachが ‘Fernando Santos’ となる試合日mdateとチーム名teamnameを表示。

game と eteam を結合するには、
game JOIN eteam ON (team1=eteam.id)
または、
game JOIN eteam ON (team2=eteam.id) id

game と eteam で同じ名前なので、
単にidと書く代わりにeteam.idと書かねばならないことに注意する。

SELECT game.mdate, eteam.teamname FROM game 
JOIN eteam ON eteam.id = game.team1 
WHERE coach = 'Fernando Santos'

7. ‘National Stadium, Warsaw’ スタジアムで開催された試合でゴールした選手を表示する。

SELECT goal.player FROM goal 
JOIN game ON game.id = goal.matchid 
WHERE game.stadium = 'National Stadium, Warsaw'

8. 【難問】ドイツと対戦して、ゴールした選手の名前を全て表示する。

(例)ドイツ―ギリシャ戦の全試合を表示するクエリー

SELECT player, gtime FROM game 
JOIN goal ON matchid = id 
WHERE (team1='GER' AND team2='GRE'

DISTINCTでplayer名の重複を無くし
ドイツプレイヤーを除くために
AND teamid <> 'GER'を追加

team1とteam2の両方に対してWHERE句を行わないと
ドイツと対戦する条件は満たせない

回答

Q: ドイツと対戦して、ゴールした選手の名前を全て表示する。

A:

SELECT DISTINCT goal.player FROM game 
JOIN goal ON goal.matchid = game.id 
WHERE (game.team1='GER' OR game.team2='GER') AND teamid <> 'GER'

9. チーム名teamnameとゴール数の合計を表示する。

SELECT teamname, count(teamid) FROM goal 
JOIN eteam ON eteam.id=goal.teamid GROUP By teamname

10. スタジアムstadiumの名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する

SELECT game.stadium, count(gtime) FROM game 
JOIN goal ON goal.matchid = game.id Group by stadium

11. ポーランド（POL）が参戦している全試合の matchid と 日程 とその試合のゴール数 を表示する。

SELECT matchid, MIN(mdate), COUNT(*) FROM game 
JOIN goal ON (game.id = goal.matchid) 
WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY goal.matchid

12. ドイツ’GER’が得点した試合の matchid と 日程 と ドイツの得点 を表示する。

MIN(game.mdate)としないと、複数検索にヒットするため、
gisq.game.mdate' isn't in GROUP BYと怒られる

SELECT goal.matchid, MIN(game.mdate), count(*) FROM game 
JOIN goal ON (goal.matchid = game.id) 
WHERE goal.teamid = 'GER' GROUP by goal.matchid

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

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

mdate team1 score1 team2 score2 
1 July 2012 ESP 4 ITA 0 
10 June 2012 ESP 1 ITA 1 
10 June 2012 IRL 1 CRO 3

注意） 全得点状況が、記録されている。もし、チーム名が goal に記録されていれば、 その時点でチームが１得点していることになり、チーム名が記載されていなければ、得点は０点である。
　チーム名有り →　１
　チーム名なし →　０
この、得点状況を １と０ に CASE WHEN で変換した結果をSUM で集計すれば、そのチームの得点を集計できる。
結果は、日程順で並べ替えて出力する(日程が同じなら、idの順番)。

JOINだと内部結合（INNER JOIN）のため、 24 June 2012や27 June 2012のデータが空なため、表示されません。 LEFT JOINを使用して、外部結合にしましょう。

A:

SELECT min(mdate), min(team1) AS team1, 
SUM( CASE WHEN goal.teamid=team1 THEN 1 ELSE 0 END) AS score1, 
min(team2) as team2, 
SUM( CASE WHEN goal.teamid=team2 THEN 1 ELSE 0 END) AS score2 FROM game 
LEFT JOIN goal ON (goal.matchid = game.id) GROUP by game.id 
ORDER BY mdate, matchid, team1, team2