[MySQL] GROUP_CONCAT 関数の結果をカラム別にして表示する

ある案件で、mysql の 便利関数 GROUP_CONCAT でまとめた複数レコードの情報を、カラム別にして表示したいことがありました。
GROUP_CONCAT 自体にそのような機能はありませんが、ちょっと工夫をすることで結果として出力したい結果を得ることができました。

こんな感じのテーブルが 2 つあるとします。

mysql> desc creep;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(10)      | NO   |     | NULL    |                |
| description | varchar(30)      | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from creep;
+----+------------+----------------------+
| id | title      | description          |
+----+------------+----------------------+
|  1 | HE IS MINE | ライブで盛り上がるよ |
|  2 | 手と手     | かなり好きな曲だよ   |
|  3 | 傷つける   | 泣けるよ             |
+----+------------+----------------------+
3 rows in set (0.00 sec)

mysql> desc hyp;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| creep_id | int(10) unsigned    | NO   |     | NULL    |                |
| type     | tinyint(3) unsigned | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from hyp;
+----+----------+------+
| id | creep_id | type |
+----+----------+------+
|  1 |        1 |    1 |
|  2 |        2 |    2 |
|  3 |        3 |    1 |
|  4 |        3 |    2 |
+----+----------+------+

creep テーブルを親として、hyp テーブルのレコードが子としてぶら下がります（creep.id = hyp.creep_id）
hyp テーブルの type には上記のように 1 か 2 が入り、creep テーブル 1 レコードに対して、 hyp テーブルには type=1 もしくは type=2 のレコードが入ります。

で、これをブラウザ上でこんな風に表示したくなりました。

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

title	description	type=1	type=2
HE IS MINE	ライブで盛り上がるよ	○	-
手と手	かなり好きな曲だよ	-	○
傷つける	泣けるよ	○	○

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

これを実現するために、いくつかの SQL を試してみました。

とりあえず GROUP BY でまとめてみる

mysql> SELECT     creep.title,
    ->            creep.description,
    ->            hyp.type
    -> FROM       creep
    -> INNER JOIN hyp
    -> ON         creep.id = hyp.creep_id
    -> WHERE      1;

+------------+----------------------+------+
| title      | description          | type |
+------------+----------------------+------+
| HE IS MINE | ライブで盛り上がるよ |    1 |
| 手と手     | かなり好きな曲だよ   |    2 |
| 傷つける   | 泣けるよ             |    1 |
| 傷つける   | 泣けるよ             |    2 |
+------------+----------------------+------+
4 rows in set (0.00 sec)

当然、こんな感じに「傷つける」が 2 行になっちゃう。

GROUP_CONCAT を使う

mysql> SELECT     creep.title,
    ->            creep.description,
    ->            GROUP_CONCAT(hyp.type) AS types
    -> FROM       creep
    -> INNER JOIN hyp
    -> ON         creep.id = hyp.creep_id
    -> WHERE      1
    -> GROUP BY   hyp.creep_id;

+------------+----------------------+-------+
| title      | description          | types |
+------------+----------------------+-------+
| HE IS MINE | ライブで盛り上がるよ | 1     |
| 手と手     | かなり好きな曲だよ   | 2     |
| 傷つける   | 泣けるよ             | 2,1   |
+------------+----------------------+-------+
3 rows in set (0.00 sec)

受け取ったアプリケーション側で工夫が必要。

こんな方法を使うとカラム別に分けられる

mysql> SELECT     creep.title,
    ->            creep.description,
    ->            max(case hyp.type when 1 then 1 end) as type_1,
    ->            max(case hyp.type when 2 then 1 end) as type_2
    -> FROM       creep
    -> INNER JOIN hyp
    -> ON         creep.id = hyp.creep_id
    -> WHERE      1
    -> GROUP BY   hyp.creep_id;
+------------+----------------------+--------+--------+
| title      | description          | type_1 | type_2 |
+------------+----------------------+--------+--------+
| HE IS MINE | ライブで盛り上がるよ |      1 |   NULL |
| 手と手     | かなり好きな曲だよ   |   NULL |      1 |
| 傷つける   | 泣けるよ             |      1 |      1 |
+------------+----------------------+--------+--------+

hyp.creep_id でグルーピングしたレコード達の hyp.type の最大値が 1 なら type_1 に 1 を、最大値が2なら type_2 に 1 を立てる。といった使い方をしています。
グルーピングした結果、hyp.type がすべてNULL なら then に入らず NULL となります。

参考：
RDBの縦持ちテーブルと横持ちテーブル、およびその変換について

※ 今回のテーブル構成はわかりやすく簡易化しています。これくらいなら1テーブルにまとめればいいじゃんという突っ込みはナシでお願いします^^;

おしまい