いきなりですが、こんな構成のテーブル群があったりします。左記の図では、ユーザー情報を管理する、ユーザーテーブル、およびそのユーザーが持っているスキル を格納しているスキルテーブル、ユーザーとスキルテーブルは、１対多で紐づいています。みたいなよくあるスキーマ構成です。 じゃあ、これを、１レコードで表現してほしいといわれたら、どうやってＳＱＬで書く？プログラムで書くのが一番の近道で簡単なのですが、一時的なものに対してプログラムをいちいち書くのは正直なところ面倒・・・でも、それが、SQLだけで簡単に実現できるのです。そしてそれを実現することができるのが、group_concat関数なのれす！！
・上記のERに沿った、テーブルを下記の手順で作成してみました。
CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_name varchar(100) DEFAULT NULL COMMENT '氏名',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
insert into users
(user_name )
values
('○○君'),
('××君'),
('△△君');
mysql> select * from users;
+-------+-----------+
| id | user_name |
+-------+-----------+
| 10001 | ○○君 |
| 10002 | ××君 |
| 10003 | △△君 |
+-------+-----------+
3 rows in set (0.00 sec)
CREATE TABLE skill (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
skill_word varchar(100) DEFAULT NULL COMMENT 'スキル',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into skill
(user_id, skill_word)
values
( 10001, '火を噴くことが出来る'),
( 10001, 'モンスターを召喚できる'),
( 10001, 'バナナで釘を打てる'),
( 10001, 'トランスフォームすることが出来る'),
( 10002, 'IBM DB2認定資格者'),
( 10002, 'ネットワークスペシャリスト'),
( 10002, 'ORACLE MASTER GOLD'),
( 10003, '早寝早起き'),
( 10003, 'ちゃんと挨拶ができる'),
( 10003, 'その場の雰囲気を凍らせることができる');
mysql> select * from skill;
+----+---------+--------------------------------------------------------+
| id | user_id | skill_word |
+----+---------+--------------------------------------------------------+
| 1 | 10001 | 火を噴くことが出来る |
| 2 | 10001 | モンスターを召喚できる |
| 3 | 10001 | バナナで釘を打てる |
| 4 | 10001 | トランスフォームすることが出来る |
| 5 | 10002 | IBM DB2認定資格者 |
| 6 | 10002 | ネットワークスペシャリスト |
| 7 | 10002 | ORACLE MASTER GOLD |
| 8 | 10003 | 早寝早起き |
| 9 | 10003 | ちゃんと挨拶ができる |
| 10 | 10003 | その場の雰囲気を凍らせることができる |
+----+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)
では、本題である、SQLで一行でこれを表現するSQLはどのように書くのか？このように書きます！
select us.id, us.user_name, rtrim(group_concat(sk.skill_word)) as skill_list from users us inner join skill sk on us.id = sk.user_id group by us.id order by sk.user_id\G
・結果どうなるのか？
*************************** 1. row *************************** id: 10001 user_name: ○○君 skill_list: 火を噴くことが出来る,トランスフォームすることが出来る,モンスターを召喚できる,バナナで釘を打てる *************************** 2. row *************************** id: 10002 user_name: ××君 skill_list: ネットワークスペシャリスト,ORACLE MASTER GOLD,IBM DB2認定資格者 *************************** 3. row *************************** id: 10003 user_name: △△君 skill_list: 早寝早起き,ちゃんと挨拶ができる,その場の雰囲気を凍らせることができる 3 rows in set (0.00 sec)
すげーーーー楽！何が楽って、skill_wordを自動でグルーピングしてくれるところです。デフォルトの設定ですと、skill_wordの区切り文字は’（カンマ）ですがSEPARATORを指定することにより、任意の区切り文字を利用することができます。
・区切り文字に"<BR>"を使ってみる
select us.id, us.user_name, rtrim(group_concat(sk.skill_word SEPARATOR '<BR>')) as skill_list from users us inner join skill sk on us.id = sk.user_id group by us.id order by sk.user_id\G
・結果は以下の通り
*************************** 1. row *************************** id: 10001 user_name: ○○君 skill_list: 火を噴くことが出来る<BR>トランスフォームすることが出来る<BR>モンスターを召喚できる<BR>バナナで釘を打てる *************************** 2. row *************************** id: 10002 user_name: ××君 skill_list: ネットワークスペシャリスト<BR>ORACLE MASTER GOLD<BR>IBM DB2認定資格者 *************************** 3. row *************************** id: 10003 user_name: △△君 skill_list: 早寝早起き<BR>ちゃんと挨拶ができる<BR>その場の雰囲気を凍らせることができる 3 rows in set (0.00 sec)
もうちょっとひやってみます。今度は、skill_wordをORDER BYして並び変えてみます。上記の例だとあまり意味がないですが、例えばこれが、優先順位とかだと効果があるかと思います。
・区切り文字に"@"を利用し、skill_listは、skillテーブルのidを降順にソートしてグルーピングする
select us.id, us.user_name, group_concat(sk.skill_word order by sk.id desc separator "@") as skill_list from users us inner join skill sk on us.id = sk.user_id group by us.id order by sk.user_id\G
・結果は以下の通り
*************************** 1. row *************************** id: 10001 user_name: ○○君 skill_list: トランスフォームすることが出来る@バナナで釘を打てる@モンスターを召喚できる@火を噴くことが出来る *************************** 2. row *************************** id: 10002 user_name: ××君 skill_list: ORACLE MASTER GOLD@ネットワークスペシャリスト@IBM DB2認定資格者 *************************** 3. row *************************** id: 10003 user_name: △△君 skill_list: その場の雰囲気を凍らせることができる@ちゃんと挨拶ができる@早寝早起き 3 rows in set (0.00 sec)
skill_listの中が、skill.id の降順でソートされているのがわかるかと思います。
いままでこんな便利が関数があったのを知らなかったなんて・・ちょっと勿体無い気分です。
MySQLを利用している方は是非ともお試しあれ。
