考え方だけ。
やり方はたくさんあります。が基本的に
1.「こんなテーブルが存在したら簡単にできる」とういう状態を考える
2.サブクエリと集計関数でほしい"こんなテーブル"の形を作る
3.2て作った形を1に代入する。
と考えると考えやすい。
部品をつくって組み上げていくという意味では普通の手続き型プログラムと考え方は同じです。
通常のプログラムがモジュールを作って組み上げていくのにたいし、SQLはサブクエリ等"部分集合"をつくって組み上げる。
方法その1:
テーブルBが20016年と20017年で別だったら?と考える
テーブルBがテーブルB_2016、テーブルB_2017という2テーブルだったら簡単。ただの結合です。
SQL
1//テーブルB_2016、テーブルB_2017があれば動くSQL
2SELECT a.出席番号, a.名前, b1.身長 AS 身長2016, b2.身長 AS 身長2017
3FROM テーブルA a
4LEFT JOIN テーブルB_2016 b1 ON b1.ID = a.ID
5LEFT JOIN テーブルB_2017 b2 ON b2.ID = a.ID
ここまで作ってテーブルB_2016、テーブルB_2017をサブクエリで作れば良い。
SQL
1//テーブルB_2016と同じ
2SELECT ID,身長 FROM テーブルB WHERE 年=2016
3//テーブルB_2017と同じ
4SELECT ID,身長 FROM テーブルB WHERE 年=2017
これを置き換えればできあがり
SQL
1//完成
2SELECT a.出席番号, a.名前, b1.身長 AS 身長2016, b2.身長 AS 身長2017
3FROM テーブルA a
4LEFT JOIN (SELECT ID,身長 FROM テーブルB WHERE 年=2016) b1 ON b1.ID = a.ID
5LEFT JOIN (SELECT ID,身長 FROM テーブルB WHERE 年=2017) b2 ON b2.ID = a.ID
方法その2:
こんなテーブルがあると楽。
テーブルX
ID | 身長2016 | 身長2017 |
---|
1 | 150 | 151 |
2 | 142 | 145 |
これなら
SQL
1//テーブルXがあれば動く
2SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
3FROM テーブルA a
4LEFT JOIN テーブルX x ON x.ID = a.ID
でできる。テーブルXはどうすればできるかかんがえると、こんなテーブルがあれば良い。
テーブルY
ID | 年 | 身長2016 | 身長2017 |
---|
1 | 2016 | 150 | 0 |
1 | 2017 | 0 | 151 |
2 | 2016 | 142 | 0 |
2 | 2017 | 0 | 145 |
テーブルXはテーブルYがあれば
SQL
1//テーブルYからテーブルXをつくる
2SELECT ID,SUM(身長2016) AS 身長2016,SUM(身長2017) AS 身長2017 FROM テーブルY GROUP BY ID
3または
4SELECT ID,MAX(身長2016) AS 身長2016,MAX(身長2017) AS 身長2017 FROM テーブルY GROUP BY ID
でできる。で、テーブルYはテーブルBをつかって
SQL
1//テーブルBからテーブルYをつくる
2SELECT
3ID,
4CASE WHEN 年=2016 THEN 身長 ELSE 0 END AS 身長2016,
5CASE WHEN 年=2017 THEN 身長 ELSE 0 END AS 身長2017
6FROM テーブルB
でできる。これを最初のSQLに代入すれば
SQL
1//完成(改良前)
2SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
3FROM テーブルA a
4LEFT JOIN
5(SELECT ID,
6MAX(身長2016) AS 身長2016,
7MAX(身長2017) AS 身長2017 FROM
8(SELECT
9ID,
10CASE WHEN 年=2016 THEN 身長 ELSE 0 END AS 身長2016,
11CASE WHEN 年=2017 THEN 身長 ELSE 0 END AS 身長2017
12FROM テーブルB) y GROUP BY ID) x ON x.ID = a.ID
これでも動くが、X,Yはまとめられるので
SQL
1//完成
2SELECT a.出席番号, a.名前, x.身長2016, x.身長2017
3FROM テーブルA a
4LEFT JOIN
5(SELECT ID,
6MAX(CASE WHEN 年=2016 THEN 身長 ELSE 0 END) AS 身長2016,
7MAX(CASE WHEN 年=2017 THEN 身長 ELSE 0 END) AS 身長2017
8FROM テーブルB GROUP BY ID) x ON x.ID = a.ID
性能とか考えるとまた別の話も必要ですが、とりあえずわりと汎用性の高い考え方の一つ。