6. JOIN /SQL ZOOの答えと解説 | まるっと情報局

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

1. matchid と player 名をドイツ(Germany)チームの全ゴールについて表示する。
- *の使い方
- 回答
2. 試合 1012 の id, stadium, team1, team2 を表示する。
3. ドイツの全ゴールについて player, teamid ,stadium, mdate を表示するように修正する。
- JOINで結びつける
4. Marioという名前の選手のゴールについて、team1, team2 , player を表示する。
5. 最初の１０分間でゴールしたという条件で、 player, teamid, coach, gtime を表示。
6. team1のコーチcoachが ‘Fernando Santos’ となる試合日mdateとチーム名teamnameを表示。
7. ‘National Stadium, Warsaw’ スタジアムで開催された試合でゴールした選手を表示する。
8. 【難問】ドイツと対戦して、ゴールした選手の名前を全て表示する。
- 回答
9. チーム名teamnameとゴール数の合計を表示する。
10. スタジアムstadiumの名前と、そのスタジアムでのゴール数を各スタジアムごとに表示する
11. ポーランド（POL）が参戦している全試合の matchid と日程とその試合のゴール数を表示する。
12. ドイツ’GER’が得点した試合の matchid と日程とドイツの得点を表示する。
13. 下に示す様に、各試合ごとに各チームの得点を表示する。

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 を表示する。

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: ドイツと対戦して、ゴールした選手の名前を全て表示する。

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を使用して、外部結合にしましょう。

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

終わり

mysql sql_zoo