概要
グループ化された行のカラムの内容も表示したい時などに使うGROUP_CONCAT()について記載します
実行環境:MySQL5.6
SQL
CREATE TABLE classroom ( classroom_id INT PRIMARY KEY, classroom_name VARCHAR(50) NOT NULL, capacity INT NOT NULL ); CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(50) NOT NULL, classroom_id INT ); INSERT INTO classroom (classroom_id, classroom_name, capacity) VALUES (1, 'Classroom A', 30), (2, 'Classroom B', 20), (3, 'Classroom C', 25); INSERT INTO student (student_id, student_name, classroom_id) VALUES (1, 'Alice', 1), (2, 'Bob', 1), (3, 'Charlie', 2), (4, 'David', 2), (5, 'Eve', 3), (6, 'Frank', 3); select classroom_name, group_concat(student_name) from classroom inner join student on classroom.classroom_id = student.classroom_id group by classroom.classroom_id ;
結果
classroom_nameに紐づくstudent_nameが連結して出力されています
ちなみにCreateとInsertはChatGPTに用意してもらいました、便利ですね
classroom_name | group_concat(student_name) |
---|---|
Classroom A | Alice,Bob |
Classroom B | Charlie,David |
Classroom C | Frank,Eve |