2014年3月16日日曜日

sql select文 スキルアップ

◆ 目的
SQLでSELECT文を実行する際に必要に応じて複数のテーブルを内部結合、外部結合しデータを抽出できているだろうか。

 ここでは試験的に簡易的な、しかしよくある典型的なテーブルを作成し、そこから意図したデータを選び出せるかを確認する。また一見正しそうな、しかし間違ったSELECT文も合わせて記載するので確認してほしい。

◆ 試験用テーブルの作成
● テーブル概要
教師テーブル
　教師ID　教師名

 学生テーブル
　学生ID　学生名

 授業テーブル
　授業ID　授業名　教師ID

 学生と授業を紐づけテーブル
　学生ID　授業ID

 なぜこういうテーブル構成なのかと疑問をもった場合は
"SQLデータベース設計の基本総おさらい"にて復習。
● テーブルの作成とデータの投入
※mysqlを想定する

 ・教師テーブルの作成
create table Teachers (
TeacherID INT NOT NULL AUTO_INCREMENT,
TeacherName VARCHAR(255) NOT NULL,
PRIMARY KEY(TeacherID));

 ・教師テーブルへの投入
insert into Teachers (TeacherName) values('t1');
insert into Teachers (TeacherName) values('t2');
insert into Teachers (TeacherName) values('t3');
insert into Teachers (TeacherName) values('t1');
・生徒テーブルの作成
create table Students (
StudentID INT NOT NULL AUTO_INCREMENT,
StudentName VARCHAR(255) NOT NULL,
PRIMARY KEY(StudentID));

 ・生徒テーブルへの投入
insert into Students (StudentName) values('s1');
insert into Students (StudentName) values('s2');
insert into Students (StudentName) values('s3');
insert into Students (StudentName) values('s4');
insert into Students (StudentName) values('s1');
・授業テーブルの作成
create table Courses (
CourseID INT NOT NULL AUTO_INCREMENT,
CourseName VARCHAR(255) NOT NULL UNIQUE,
TeacherID INT NOT NULL,
PRIMARY KEY(CourseID));

 ・授業テーブルへの投入
insert into Courses (CourseName, TeacherID) values('c1', 1);
insert into Courses (CourseName, TeacherID) values('c2', 3);
insert into Courses (CourseName, TeacherID) values('c3', 1);
insert into Courses (CourseName, TeacherID) values('c4', 4);
insert into Courses (CourseName, TeacherID) values('c5', 2);
insert into Courses (CourseName, TeacherID) values('c6', 1);
・学生と授業の紐づけテーブルの作成
CREATE TABLE StudentCourses (
StudentID INT NOT NULL,
CourseID INT NOT NULL);

 ・学生と授業の紐づけテーブルの投入
授業を一つも選択していない学生がいるところがポイントである。

 insert into StudentCourses values(1, 1);
insert into StudentCourses values(1, 3);
insert into StudentCourses values(1, 4);

 insert into StudentCourses values(2, 2);
insert into StudentCourses values(2, 5);

 insert into StudentCourses values(3, 3);

◆ すべての学生名と、各学生が登録している授業の数を出力
下のように表示させたい。

 +-------------+-----------+-----+
| StudentName | StudentID | sum |
+-------------+-----------+-----+
| s1          |         1 |   3 |
| s2          |         2 |   2 |
| s3          |         3 |   1 |
| s4          |         4 |   0 |
| s1          |         5 |   0 |
+-------------+-----------+-----+
※s1は同性の別人である。

 このあたりのテーブルをうまく組み合わせればいけそうである。
授業をとっていない学生もいることを忘れてはならない。
学生テーブル
　学生ID　学生名

 学生と授業を紐づけテーブル
　学生ID　授業ID
● 失敗例
SELECT Students.StudentName, Students.StudentID, count(*) as sum
FROM Students INNER JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID;

 +-------------+-----------+-----+
| Studentname | StudentID | sum |
+-------------+-----------+-----+
| s1          |         1 |   3 |
| s2          |         2 |   2 |
| s3          |         3 |   1 |
+-------------+-----------+-----+

 授業を一つも選択していない学生が除外されてしまう。
● 失敗例
SELECT Students.StudentName, Students.StudentID, count(*) as sum
FROM Students LEFT OUTER JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID;

 +-------------+-----------+-----+
| StudentName | StudentID | sum |
+-------------+-----------+-----+
| s1          |         1 |   3 |
| s2          |         2 |   2 |
| s3          |         3 |   1 |
| s4          |         4 |   1 | ←ここ
| s1          |         5 |   1 | ←ここ
+-------------+-----------+-----+

 count(*)の結果はグループ化したStudents.StudentIDごとに集計されるので、
どの授業にも登録していない学生も数えられる。

 count(*)とGROUP BY Students.StudentIDを外した場合を見てもらうと分かりやすいだろうか。

 SELECT * FROM Students LEFT OUTER JOIN StudentCourses 
ON Students.StudentID = StudentCourses.StudentID;

 +-----------+-------------+-----------+----------+
| StudentID | StudentName | StudentID | CourseID |
+-----------+-------------+-----------+----------+
|         1 | s1          |         1 |        1 |
|         1 | s1          |         1 |        3 |
|         1 | s1          |         1 |        4 |
|         2 | s2          |         2 |        2 |
|         2 | s2          |         2 |        5 |
|         3 | s3          |         3 |        3 |
|         4 | s4          |      NULL |     NULL |
|         5 | s1          |      NULL |     NULL |
+-----------+-------------+-----------+----------+
● 成功例
SELECT Students.StudentName, Students.StudentID, count(StudentCourses.CourseID) as sum
FROM Students LEFT OUTER JOIN StudentCourses
ON Students.StudentID = StudentCourses.StudentID
GROUP BY Students.StudentID;

 +-------------+-----------+-----+
| StudentName | StudentID | sum |
+-------------+-----------+-----+
| s1          |         1 |   3 |
| s2          |         2 |   2 |
| s3          |         3 |   1 |
| s4          |         4 |   0 |
| s1          |         5 |   0 |
+-------------+-----------+-----+

 NULL値は加算されない。
● 成功例
別の手段もある。

 SELECT Students.StudentName, Students.StudentID, sum
FROM (
  SELECT Students.StudentID, count(StudentCourses.CourseID) as sum
  From Students LEFT OUTER JOIN StudentCourses 
  ON Students.StudentID = StudentCourses.StudentID 
  group by Students.StudentID
) T INNER JOIN Students on T.StudentID = Students.StudentID;
内部SELECT
+-----------+-----+
| StudentID | sum |
+-----------+-----+
|         1 |   3 |
|         2 |   2 |
|         3 |   1 |
|         4 |   0 |
|         5 |   0 |
+-----------+-----+

 +-------------+-----------+-----+
| StudentName | StudentID | sum |
+-------------+-----------+-----+
| s1          |         1 |   3 |
| s2          |         2 |   2 |
| s3          |         3 |   1 |
| s4          |         4 |   0 |
| s1          |         5 |   0 |
+-------------+-----------+-----+

◆ すべての教師と、各教師が受け持つ学生の数を出力
抽出条件を変えてみる。
このように表示させることを目標とする。
+-------------+-----------+------+
| Teachername | TeacherID | sum  |
+-------------+-----------+------+
| t1          |         1 |    3 |
| t2          |         2 |    1 |
| t3          |         3 |    1 |
| t1          |         4 |    1 |
+-------------+-----------+------+

 目をつけるテーブルはここだろう。

 授業テーブル
　授業ID　授業名　教師ID

 学生と授業を紐づけテーブル
　学生ID　授業ID

 教師名が必要なのでさらに、次のテーブルも必要になると判断できる。
教師テーブル
　教師ID　教師名
● 成功例
SELECT Teachername, T.TeacherID, T.sum
FROM Teachers INNER JOIN
(
  SELECT TeacherID, count(*) as sum
  FROM Courses INNER JOIN StudentCourses
  ON Courses.CourseID = StudentCourses.CourseID
  GROUP BY Courses.TeacherID
) T
ON Teachers.TeacherID = T.TeacherID;
内部SELECT
+-----------+-----+
| TeacherID | sum |
+-----------+-----+
|         1 |   3 |
|         2 |   1 |
|         3 |   1 |
|         4 |   1 |
+-----------+-----+

 +-------------+-----------+------+
| Teachername | TeacherID | sum  |
+-------------+-----------+------+
| t1          |         1 |    3 |
| t2          |         2 |    1 |
| t3          |         3 |    1 |
| t1          |         4 |    1 |
+-------------+-----------+------+



投稿者 時刻:
ラベル: