🌊

sqlzooで詰まった問題

2022/11/13に公開約1,700字

sqlzooをやってみたので,詰まった問題についてまとめておく.

JOIN(1)

問題:ドイツチームの全ゴールのmatchidとplayerを取得する
回答:最初はSELECT matchid, player FROM goal INNER JOIN eteam ON teamid = 'GER'というクエリを書いた.そもそもJOINのONには結合条件を指定するのが普通であり,絞り込み条件を指定している時点で間違っている.正解はSELECT matchid, player FROM goal INNER JOIN eteam ON teamid = eteam.id WHERE teamid = 'GER'になる.このクエリだと「先にgoalテーブルとeteamテーブルを結合してからteamid='GER'で絞り込みをかける」という意味になる.

JOIN(8)

問題:ドイツと試合をしてゴールを決めた選手の名前を取得する
回答:最初はSELECT player FROM goal INNER JOIN game ON goal.matchid = game.id WHERE (team1 = 'GER' AND teamid != 'GER') OR (team2 = 'GER' AND teamid != 'GER')と書いた.しかし,このクエリだと「ドイツと試合して2回以上ゴールした選手」の名前をゴールした回数分だけ出力してしまう.SELECT DISTINCT player ...とすることで,選手名が重複しないようにできる.

JOIN(9)

問題:各チームのチーム名とゴール数を取得する
回答:GROUP BYを使うのが肝.GROUP BYは指定したカラムの値が重複するレコードをまとめて出力する.指定したカラム以外のカラムの値が異なり,かつそれを出力しようとするとエラーになる(異なるカラムが存在する以上,まとめることができないため).回答はSELECT teamname, COUNT(teamname) FROM goal INNER JOIN eteam ON goal.teamid = eteam.id GROUP BY teamnameになる.

JOIN(11)

問題:ポーランドが参戦している全試合のmatchid, mdate, ゴール数を取得する
回答:おおまかな方針はすぐに分かったので簡単かと思ったが,意外とハマった.GROUP BYでmatchidを指定していたことでmdateが出力できなかった(mdateでGROUP BYすると今度はmatchidが出力できなくなる).最終的にはGLOUP BYに複数カラムを指定できることが分かり,無事解決した.回答はSELECT matchid, mdate, COUNT(matchid) FROM goal INNER JOIN game ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid, mdateになった.グループ化というのは特定の情報以外の情報を排除することなので,あくまで集計用に使うものだなあと思った.

JOIN(12)

問題:ドイツが参戦した試合のmatchid, mdate, ゴール数を取得する
回答:ほとんど11と同じだが,ドイツのゴールだけをカウントする必要がある.よって,WHEREでgoal.teamid = 'GER'という条件を追加してやればよい.

JOIN(13)

問題:各試合ごとに各チームの得点を取得する
回答:SUMをどう使えばよいのかが分からなかった.SUMの中にCASE WHEN式を書いて,それをscore1, score2とすることで解決した.ただ,末尾のGROUP BYがなぜ必要なのかはまだ理解できていない.

Discussion

ログインするとコメントできます