一、语法
group_concat( [distinct] 字段名 [order by 排序字段 asc/desc ] [Separator '分分隔符'] )
二、例子
mysql> select * from tb_test;
+----+------+
| id | name |
+----+------+
| 1 | 100 |
| 1 | 200 |
| 1 | 200 |
| 2 | 200 |
| 3 | 300 |
| 3 | 500 |
+----+------+
6 rows in set
以id分组,把那么字段的值打印在一行,都好分隔(默认)
mysql> select id,group_concat(name) from tb_test group by id;
+----+--------------------+
| id | group_concat(name) |
+----+--------------------+
| 1 | 100,200,200 |
| 2 | 200 |
| 3 | 300,500 |
+----+--------------------+
3 rows in set
以id分组,把name字段的值打印在一行,分好分隔
mysql> select id, group_concat(name separator ';') from tb_test group by id;
+----+----------------------------------+
| id | group_concat(name separator ';') |
+----+----------------------------------+
| 1 | 100;200;200 |
| 2 | 200 |
| 3 | 300;500 |
+----+----------------------------------+
3 rows in set
以id分组,把去冗余的name字段的值打印在一行
mysql> select id,group_concat(distinct name) from tb_test group by id;
+----+-----------------------------+
| id | group_concat(distinct name) |
+----+-----------------------------+
| 1 | 100,200 |
| 2 | 200 |
| 3 | 300,500 |
+----+-----------------------------+
3 rows in set
以id分组,把name字段的值打印在一行,都好分隔,以name排序
mysql> select id,group_concat(name order by name asc) from tb_test group by id;
+----+--------------------------------------+
| id | group_concat(name order by name asc) |
+----+--------------------------------------+
| 1 | 100,200,200 |
| 2 | 200 |
| 3 | 300,500 |
+----+--------------------------------------+
3 rows in set