

SQL初心者です。

下記のSQL練習サイトの13番に関する質問です。  
テーブルの連結とグループ化のSQLの作成にだいぶ煮詰まっているので  
アプローチの仕方について教えてください。  
「下に示す様に、各試合ごとに各チームの得点を表示する。 この問題は、ここまでまだ未解説のSQL構文「CASE WHEN」を使用する。」  
<http://sqlzoo.net/wiki/The_JOIN_operation/ja>

team1とteam2に対して、goalテーブルからスコアを集計(GROUP BY)してあげなければいけないところまでは分かっているんですが  
gameテーブルとgoalテーブルをどう分割し、どうグループ化したら良いのかわかりません。。

ここまでは出来ています。

[code]

    SELECT ga.mdate,ga.team1
      FROM game ga ,goal go1
        WHERE ga.id = go1.matchid
          AND ga.team1 = go1.teamid
        ORDER BY ga.mdate
    
[/code]

## 追記

user20098さん

ご返答ありがとうございます。  
可能であれば、勉強のために  
解答例示していただけないでしょうか。

以下のように、全ての試合のチーム名とゴールしたチーム名を  
出すところまでは出来たんですが…  
`[CASE WHEN]`を使用してteam1とteam2の得点を集計するやり方が分からないです。  
(提示していただいたポイントの一番下しかできていないです。)

[code]

    SELECT ga.mdate,ga.team1 ,ga.team2,go.teamid as goalTeam
    FROM game ga
    LEFT OUTER JOIN goal go ON ga.id = go.matchid
    ORDER BY ga.mdate , ga.id
    
[/code]


CC BY-SA 4.0
2018-12-08T05:28:49.533
51023
2018-12-09T15:06:41.173
2018-12-09T15:06:41.173
19110
31355
question
sql
テーブルの連結とグループ化のSQLの作成


＃当該ページの趣旨としてよろしくないと思うので、正解ずばり回答するのは一旦避けます。ポイントだけ。

基本はページ記載の例を拡張すればよいと思います。ポイントは次の3点と考えます。

  * SQL文に`team1`に対する`CASE WHEN`の記載があると思いますが、同じ感じで`team2`についても記載する
  * SQL文(select文の結果)を一時テーブルとして、一時テーブルを`select`して一時テーブルの`score1`、`score2`で集計(`sum`)する
  * 試合によっては両チーム得点なし(`goal`テーブルにレコードがない)場合があるので、`game`と`goal`を`INNER JOIN`するとそういう試合が出力されないので、2テーブルの結合を工夫する

参考: ページ記載のSQL。

[code]

    SELECT mdate,
      team1,
      CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
      FROM game JOIN goal ON matchid = id
    
[/code]

参考になれば幸いです。

* * *

# 追記

正解と判定されるSQL文です。

[code]

    SELECT mdate,team1,sum(score1) score1,team2,sum(score2) score2 FROM (
        SELECT mdate,
          team1,
          CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1,
          team2,
          CASE WHEN teamid=team2 THEN 1 ELSE 0 END score2
          FROM game LEFT OUTER JOIN goal ON matchid = id
    ) t
    group by mdate,team1,team2
    order by mdate
    
[/code]

＃他の手段もあると思いますが、SQLの例を問題文のヒントに沿って拡張するとこういうSQL文になると思います。


CC BY-SA 4.0
2018-12-08T09:02:53.833
51037
2018-12-09T14:20:50.707
2018-12-09T14:20:50.707
20098
20098
51023
answer