はじめに
多対多のソートで手こずったので、備忘録として残します。
やりたいこと
多対多で、もう片方のテーブル先の値で一覧をソートしたい。
大学生の履修登録アプリを例にします。
学生は複数の講義に登録でき、また講義も複数の学生を持つことができます。
次のER図より、「講義テーブル」と「学生テーブル」が「履修登録テーブル」によって接続されていることがわかります。
教授から「自分の講義にはどの学生がくるっけなぁ...学生コード順に知りたいな」という要望があるとします。
そこで、講義テーブルの一覧を表示しつつ、講義ごとに学生コード順に並び替えたい時のクエリを考えます。
多対多の並び替えSQL
SELECT
c.*,
GROUP_CONCAT(s.student_code ORDER BY s.student_code SEPARATOR '/') AS コースを履修する学生 -- 講義テーブルに紐づく学生コードの集合
FROM
courses c
LEFT JOIN
student_course_enrollments sce -- 中間テーブルの結合
ON c.course_id = sce.course_id
LEFT JOIN
students s
ON sce.student_id = s.student_id
GROUP BY
c.course_id -- 講義ごとにグループ化
ORDER BY
コースを履修する学生
;
結果
解説
ポイントはGROUP_CONCAT関数とクエリの実行順序です。
クエリの実行順序はFROM→GROUP BY→SELECT→ORDER BYの順で実行されます。
まず、FROMでは講義テーブルを呼び出し、さらに講義テーブルに紐づく、履修登録テーブル、学生テーブルを結合します。
次にGROUP BYで講義IDの集合を作成します。FROMの段階で、履修登録テーブル、学生テーブルが結合しているため、講義IDの集合に合わせて値が仕分けされます。
SELECTでは、GROUP_CONCATを使って、講義IDの集合に対して紐づく値をまとめています。
まとめた値をORDER BYで並び替えをしています。
GROUP_CONCAT
GROUP_CONCAT(s.student_code ORDER BY s.student_code SEPARATOR '/') AS コースを履修する学生
GROUP_CONCATはMySQLに用意されている集計関数の一種で、GROUP BYで同じデータを集約した時に、NULL以外の値を含む文字列を連結して返してくれます。また集合が存在しない場合NULLを返します。
SEPARATORにより、各講義に登録されている学生のstudent_codeを'/'で区切って連結します。
GROUP BY
GROUP BY c.course_id
講義ごとにグループ化します。これにより、各コースに対する学生リストを集約できます。
ORDER BY
ORDER BY
コースを履修する学生
SELECT文で「コースを履修する学生カラム」を作成したので、/を含んだ文字列で並び替えができます。
（'S001/S002'の状態で並び替えしています。）
まとめ
多対多の結合先での並び替えクエリを記載しました。
何かの参考になれば幸いです。
参考
おまけ
ER図のPluntUML記法
@startuml
' hide the spot
hide circle
' avoid problems with angled crows feet
skinparam linetype ortho
entity "履修登録" as e01 {
*e1_id : number <<generated>>
--
*name : text
description : text
}
entity "学生" as e02 {
student_id : int <<PK>>
--
name : varchar
student_code: varchar
}
entity "コース" as e03 {
course_id : number <<PK>>
--
course_name : varchar
description: text
}
e01 |o..o{ e02
e01 |o..o{ e03
@enduml
@enduml
SQL
-- テーブル作成
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100),
student_code VARCHAR(20)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
description text,
credits INT
);
CREATE TABLE student_course_enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- ダミーデータ
INSERT INTO students (student_id, name, student_code) VALUES (1, '佐藤 健', 'S001');
INSERT INTO students (student_id, name, student_code) VALUES (2, '鈴木 花子', 'S002');
INSERT INTO students (student_id, name, student_code) VALUES (3, '高橋 一郎', 'S003');
INSERT INTO students (student_id, name, student_code) VALUES (4, '田中 美咲', 'S004');
INSERT INTO students (student_id, name, student_code) VALUES (5, '伊藤 太郎', 'S005');
INSERT INTO students (student_id, name, student_code) VALUES (6, '山本 あゆみ', 'S006');
INSERT INTO students (student_id, name, student_code) VALUES (7, '中村 陽子', 'S007');
INSERT INTO students (student_id, name, student_code) VALUES (8, '小林 翔太', 'S008');
INSERT INTO students (student_id, name, student_code) VALUES (9, '加藤 絵理', 'S009');
INSERT INTO students (student_id, name, student_code) VALUES (10, '吉田 真也', 'S010');
INSERT INTO courses (course_id, course_name, description) VALUES (1, '総合演習II', '実践的なプロジェクトを通じて、ソフトウェア開発の全体的な流れを学びます。');
INSERT INTO courses (course_id, course_name, description) VALUES (2, 'Java基礎', 'Java言語の基礎から応用までを学び、オブジェクト指向プログラミングの概念を理解します。');
INSERT INTO courses (course_id, course_name, description) VALUES (3, 'オペレーティングシステム', '現代のOSの構造と機能、プロセス管理などを学び、実践的な知識を身に付けます。');
INSERT INTO courses (course_id, course_name, description) VALUES (4, 'ウェブ技術の基礎', 'HTML、CSS、JavaScriptを学び、基本的なウェブサイトを作成する方法を習得します。');
INSERT INTO courses (course_id, course_name, description) VALUES (5, 'セキュリティ基礎', '情報セキュリティの基本原則と脆弱性について学び、対策技術について理解を深めます。');
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 1);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (2,1);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (3, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (4, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (5, 2);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (1, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (2, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (3, 3);
INSERT INTO student_course_enrollments (student_id, course_id) VALUES (4, 3);