Blog Alpha Networking: 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 |
+-------------+-----------+------+

2014年3月16日日曜日

sql select文 スキルアップ

sql select文スキルアップ