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 |
+-------------+-----------+------+





0 件のコメント:

コメントを投稿