GROUP_CONCAT関数の便利さは異常

MySQL

http://dev.mysql.com/doc/refman/5.1/ja/group-by-functions.html

GROUP_CONCAT(expr)
この関数は、グループからの連結された非 NULL 値を伴うストリング結果を戻します。非NULL 値がない場合は NULL を戻します。MySQL では、式のコンビネーションの連結された値を得ることができます。DISTINCT を使用することで、重複した値を除くことが可能です。結果の値をソートしたい場合は、ORDER BY 句を使用してください。逆順でソートするには、DESC ( 降順 ) キーワードを、ORDER BY 句のソートするカラムの名前に加えてください。デフォルトでは昇順になっています。これは、ASC を使うことで明示的に指定することができます。SEPARATOR の後には、結果の値の間に挿入されるべきストリング値が続きます。デフォルトは コンマ ( ‘,’ ) です。SEPARATOR '' を使用すると、セパレータを一挙に取り除くことができます。

1対多の関係を持つ親子テーブルがあった場合、1つの主キーに対して結合結果が複数行になることはよくあると思いますが、MySQLでは上記の関数を使うことで複数行内の列をひとつの文字列としてまとめることができます。

ではここで例を。まずは社員が複数の部署に所属するような形式のテーブルを作成。

mysql> create table dept(id int, name varchar(60));
mysql> create table emp(id int, name varchar(60));
mysql> create table dept_emp( empid int, deptid int);

部署マスタ(親)、社員マスタ(親)、社員部署対応マスタ(子)からなるシンプルな構成。
これにテストデータを投入。

mysql> insert into emp values ( 1, 'john');
mysql> insert into emp values ( 2, 'scott');
mysql> insert into emp values ( 3, 'smith');
mysql> insert into dept values ( 1, 'market');
mysql> insert into dept values ( 2, 'hrm');
mysql> insert into dept values ( 3, 'sales');
mysql> insert into dept_emp values(1, 1);
mysql> insert into dept_emp values(1, 2);
mysql> insert into dept_emp values(2, 2);
mysql> insert into dept_emp values(3, 1);
mysql> insert into dept_emp values(3, 2);
mysql> insert into dept_emp values(3, 3);

見ての通り、johnはmarket, hrmに所属しているので2行として出ます。

mysql> select dept_emp.empid, emp.name, dept.name
-> from dept_emp left join emp on dept_emp.empid=emp.id
-> left join dept on dept_emp.deptid=dept.id
-> order by dept_emp.empid;

empid name name
1 john market
1 john hrm
2 scott hrm
3 smith hrm
3 smith market
3 smith sales

この複数行の結果をつなげてカンマ区切りの文字列にしたい場合、GROUP_CONCAT関数が便利です。これを使うとgroup by単位でまとめて、カンマ区切りの文字列に結合してくれます。GROUP_CONCAT内でdistinctやordey byもかけられるので大半の要件は満たせるはずです。

mysql> select dept_emp.empid, emp.name, GROUP_CONCAT(dept.name order by dept.id)
-> from dept_emp left join emp on dept_emp.empid=emp.id
-> left join dept on dept_emp.deptid=dept.id
-> group by dept_emp.empid
-> order by dept_emp.empid;

empid name GROUP_CONCAT(dept.name order by dept.id)
1 john market,hrm
2 scott hrm
3 smith market,hrm,sales

Oracleでも出来なくはないんですが、かなり面倒なんですよねぇ。