SQL練習問題 – 問14

この記事は2016年7月19日に書かれたものです。内容が古い可能性がありますのでご注意ください。


SQLの練習問題です。

筆者の環境は以下となっていますが、別のデータベースやツールでもほとんど同じように動作すると思います。

  • MariaDB 5.5.48
  • MySQL Workbench 6.3 ← 現在、お試し期間中です。

よくわからないという方は、XAMPをインストールすると簡単に筆者と同じ環境を構築できます。以下のサイトで紹介されています。
WINDOWS XAMPP にて LARAVEL をインストール (XAMPP V1.8.3インストール)

問題として使用するデータは以下からダウンロード可能です。phpMyAdminからデータベース(worldcup2014)を作成しインポートしておいてください。
worldcup2014.zip

※データの誤りがありましたので、既にインポートされた方も改めて再インポートをお願いします。※ 2016/07/15


問題:グループCの各対戦毎にゴール数を表示してください。

ゴール数がゼロの場合も表示してください。副問合せは使わずに、外部結合だけを使用して下さい。

表示するカラム
・キックオフ日時
・自国名
・対戦相手国名
・自国FIFAランク
・対戦相手国FIFAランク
・自国のゴール数

ソート順
・キックオフ日時
・自国FIFAランク

WS000011

正解:

SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country,
    c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
    COUNT(g1.id) AS my_goals
FROM pairings p1
LEFT JOIN countries c1 ON c1.id = p1.my_country_id
LEFT JOIN countries c2 ON c2.id = p1.enemy_country_id
LEFT JOIN goals g1 ON p1.id = g1.pairing_id
WHERE c1.group_name = 'C' AND c2.group_name = 'C'
GROUP BY p1.kickoff, c1.name, c2.name, c1.ranking, c2.ranking
ORDER BY p1.kickoff, c1.ranking

「ゴール数がゼロの場合も表示」ということですので、メインテーブルとなるのはpairingsテーブルとします。
表示するカラムはこのpairingsテーブルとcountriesテーブルを結合(2つ)、goalsテーブルすれば全て表示することが可能です。
間違いやすいポイントとしては、以下が挙げられます。
・GROUP BY句にSELECT句で指定したカラムを全て列挙する
・my_goalsはgoalsテーブルのPKをカウントする
・決勝リーグの結果が含まれないように自国と対戦国がどちらもCグループという条件を付ける

← SQL練習問題 一覧まとめに戻る

  • このエントリーをはてなブックマークに追加

PAGE TOP