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 を表示するように修正する。
- 4. Marioという名前の選手のゴールについて、team1, team2 , player を表示する。
- 5. 最初の10分間でゴールしたという条件で、 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 を表示する。
A:
SELECT id,stadium,team1,team2 FROM game where id = 1012
3. ドイツの全ゴールについて player, teamid ,stadium, mdate を表示するように修正する。
JOINで結びつける
JOIN を利用して、2つのステップを組み合わせて単独のクエリ―にすることができる。 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. 最初の10分間でゴールしたという条件で、 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 に記録されていれば、 その時点でチームが1得点していることになり、チーム名が記載されていなければ、得点は0点である。
チーム名有り → 1
チーム名なし → 0
この、得点状況を 1と0 に 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