CTE でクエリを整理

ホームチームとアウェイチームの対戦相手を特定する 2 つのサブクエリを作成しました。次は、home と away のサブクエリを 共通テーブル式（CTE）として使い、クエリを整理しましょう。メインクエリの中にある SELECT DISTINCT というフレーズに注目してください。一意（DISTINCT ）の試合を指定しないと、各試合のレコードが重複して返される可能性があります。

引き続き、2014/2015 シーズンにマンチェスター・ユナイテッドがプレーしたすべての試合を抽出するクエリを完成させましょう。

この演習はコースの一部です

SQL のデータ操作

コースを見る

演習の手順

メインクエリの前に home と away の CTE を宣言してください。
LEFT JOIN を使って、CTE を match テーブルに結合してください。
メインクエリで使うデータを、CTE から取り出してください。
メインクエリの match テーブルから試合日、CTE からチーム名、さらに、メインクエリの match テーブルから、ホームおよびアウェイの得点を取得してください。

実践的なインタラクティブ演習

このサンプルコードを完成させて、この演習に挑戦してみましょう。

-- Set up the home team CTE
___ ___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Win'
		   WHEN m.home_goal < m.away_goal THEN 'MU Loss' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),
-- Set up the away team CTE
___ ___ (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'MU Loss'
		   WHEN m.home_goal < m.away_goal THEN 'MU Win' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)
-- Select the date, team names and goals
SELECT DISTINCT
    ___.___,
    ___.___ AS home_team_name,
    ___.___ AS away_team_name,
    ___.___,
    m.away_goal
-- Join the CTEs onto the match table
FROM match AS m
___ JOIN ___ ON m.id = ___
___ JOIN ___ ON m.id = ___
WHERE m.season = '2014/2015'
      AND (home.team_long_name = 'Manchester United' 
           OR away.team_long_name = 'Manchester United');

コードを編集して実行