MySQL group_concat函数进行行转列使用

来源：互联网发布：阿里云 api市场编辑：程序博客网时间：2024/06/13 12:18

创建测试数据:
mysql> create table group_concat (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into group_concat values(2),(1),(4),(5),(8);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from group_concat;
+------+
| a |
+------+
| 2 |
| 1 |
| 4 |
| 5 |
| 8 |
+------+
5 rows in set (0.00 sec)

基本使用:

mysql> select group_concat(a) from group_concat;
+-----------------+
| group_concat(a) |
+-----------------+
| 2,1,4,5,8 |
+-----------------+
1 row in set (0.00 sec)

简单排序:

mysql> select group_concat(a order by a) from group_concat;
+----------------------------+
| group_concat(a order by a) |
+----------------------------+
| 1,2,4,5,8 |
+----------------------------+
1 row in set (0.00 sec)

定制分隔符：

mysql> select group_concat(a order by a separator '|') from group_concat;
+------------------------------------------+
| group_concat(a order by a separator '|') |
+------------------------------------------+
| 1|2|4|5|8 |
+------------------------------------------+
1 row in set (0.00 sec)

相同记录分组:
mysql> insert into group_concat values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select group_concat(a order by a separator '|') from group_concat;
+------------------------------------------+
| group_concat(a order by a separator '|') |
+------------------------------------------+
| 1|1|2|4|5|8 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select group_concat(a order by a separator '|') from group_concat group by a;
+------------------------------------------+
| group_concat(a order by a separator '|') |
+------------------------------------------+
| 1|1 |
| 2 |
| 4 |
| 5 |
| 8 |
+------------------------------------------+
5 rows in set (0.00 sec)