プログラミング

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

sql zoo 6 join

 

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 を利用して、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

 

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です