mysql的group_concat函数使用方法

来源：互联网发布：什么是fft算法编辑：程序博客网时间：2024/05/17 08:19

一、语法

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

0 0