可視化SQL | 長いSQLを短くする CASE文、集約関数の賢い使い方

# 初めに
この問題は、SQLパズル #13 『2人かそれ以上か、それが問題だ』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

この問題は学校である科目を受講している生徒と教師の数を表示させる問題です
下記の様な形式となります

科目ID | 生徒名 | 1人目教師名 | 2人目教師名 |

条件として・・・
　[1]　各科目毎に1行で表示する事
　[2]　教師の名前は2人分表示するスペースがり、科目によって教師の数が違う
　　　　・1人の時は1人分の名前表示　　　　　　　　教師１　空白
　　　　・2人の時は2人分の名前表示　　　　　　　　教師１　教師２
　　　　・2人以上の時は1人分の名前と文字を表示　　教師１　-- More --
　[3]　先生の名前はソートして表示させる

取出したいデータは下記の様になります
各教科のIDとして、 『教科 10』『教科 20』『教科 30』 があり
教科によっては、担当の教師が1人または複数人存在しています


![rep_0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/59a71815-26cb-581c-2e44-5f6e1b6c6e68.png)


# テーブル と データ
```SQL:SQL
CREATE TABLE Register(
	course_nbr INTEGER NOT NULL,     -- 科目ID
	student_name CHAR(10) NOT NULL,  -- 生徒名
	teacher_name CHAR(10) NOT NULL   -- 教師名
);
 
INSERT INTO Register VALUES(10, 'student１', 'teacher1');
INSERT INTO Register VALUES(20, 'student１', 'teacher1');
INSERT INTO Register VALUES(20, 'student１', 'teacher2');
INSERT INTO Register VALUES(30, 'student１', 'teacher1');
INSERT INTO Register VALUES(30, 'student１', 'teacher2');
INSERT INTO Register VALUES(30, 'student１', 'teacher3');
```

# 回答SQL(その１)
```SQL:SQL
SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),NULL
FROM Register AS R1
GROUP BY R1.course_nbr,R1.student_name
HAVING COUNT(*) = 1

UNION

SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),MAX(R1.teacher_name)
FROM Register AS R1
GROUP BY R1.course_nbr,R1.student_name
HAVING COUNT(*) = 2

UNION

SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),'--More--'
FROM Register AS R1
GROUP BY R1.course_nbr, R1.student_name
HAVING COUNT(*) > 2
```

▼ 出力結果

教科10 : 教師1人だけなので、2つ目の教師名は空白
教科20 : 教師2人いるので、1人目の教師名と、2人目の教師名が入っています
教科30 : 教師が3人いるので、1人目の教師名と --More-- が入っています

|course_nbr|student_name|teacher_name|teacher_name|
|---|---|---|---|
|10|student１|teacher1|NULL|
|20|student１|teacher1|teacher2|
|30|student１|teacher1|--More--|


# 回答SQL(その１)　の解説
これはすごくシンプルなSQLです。
『教科(course_nbr)』 と 『生徒名(student_name)』 で GROUP BY した後、行数をカウントしています。
この数が教師の数と同じになります
教師の数によって、表示させる教師名をカスタマイズしています

下記画像は
[1] 左に 今回使用するテーブルの全データ
[2] 真ん中に, 科目と生徒で GROUP BY した後のデータ
[3] 右側に回答SQLを載せています


![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ea4d7a00-da27-fd16-eb8b-577c323ff25a.png)



つまり、回答SQLが行っている事は ■ 1 ～ 5 です

■ 1　`教師の数が1人の科目`を取出し
　　　⇒　HAVING COUNT(*) = 1
　　　⇒　MIN(教師名)で名前を取出しています。2人目はいないので NULL を直書き

■ 2　UNION を使って 次のデータを結合

■ 3　`教師の数が2人の科目`を取出し
　　　⇒　HAVING COUNT(*) = 2
　　　⇒　MIN(教師名)で最初の教師、MAX(教師名)で2人目の教師名の取出し
　　　⇒　名前がソートされて表示される

■ 4　UNION を使って 次のデータを結合

■ 5　`教師の数が2人以上の科目`を取出し
　　　⇒　HAVING COUNT(*) > 2
　　　⇒　MIN(教師名)で最初の教師名を取出し、2人目を --More--を直書き

注意：MIN や MAX を使わないと下記の様なエラーが出ます
　　　`teacher_nameはGROUP BY句で指定するか集約関数内で使用しなければなりません`
　　　ここで MIN と MAX を使っているのは
　　　１：**教師名が集約変数で無いため、そのままでは表示できない**
　　　２：**表示する名前をソートさせるため**


![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/25b8a2c8-a433-a8b6-c441-a60b868bec54.png)


以上となります・・・がここからが本題です

SQLの書き方によって、同じ結果を短いSQLで取得できます

# 回答SQL(その２)
```SQL:SQL
SELECT  course_nbr,student_name,
		MIN(teacher_name), -- 1人目の教師名
        CASE COUNT(*) WHEN 1 THEN NULL
                      WHEN 2 THEN MAX(teacher_name)
                      ELSE '--More--' END  -- 2人目の教師名
FROM Register
GROUP BY course_nbr, student_name
```

▼ 出力結果

|course_nbr|student_name|teacher_name|teacher_name|
|---|---|---|---|
|10|student１|teacher1|NULL|
|20|student１|teacher1|teacher2|
|30|student１|teacher1|--More--|

回答SQL(その１)と比べると、かなり短くなっています。
同じ処理をするSQLでも書き方によって可読性が高く、メンテンナンスしやすく出来る・・・と
認識させてくれるいい例だと思います

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0e3bdbe5-5ca6-a80d-6531-5175af083bfd.png)

# 参考文献
[SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)