行转列group_concat

行转列group_concat()函数妙用

测试环境准备：

mysql> create table test01(name varchar(20),address varchar(20));

Query OK, 0 rows affected (0.28 sec)

mysql> insert into test01 values('huzj','hunan');

Query OK, 1 row affected (0.06 sec)

mysql> insert into test01 values('huzj','hubei');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test01 values('huzj','jianxi');

Query OK, 1 row affected (0.01 sec)

mysql> insert into test01 values('huzj','sichuan');

Query OK, 1 row affected (0.01 sec)

mysql> insert into test01 values('huzy','sichuan');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test01 values('huzy','nibo');

Query OK, 1 row affected (0.01 sec)

mysql> insert into test01 values('huzy','shanghai');

Query OK, 1 row affected (0.00 sec)

mysql> insert into test01 values('huzy','beijing');

Query OK, 1 row affected (0.01 sec)

测试：

mysql> select name,group_concat(address) from test01 group by name;

+------+-------------------------------+

| name | group_concat(address)         |

+------+-------------------------------+

| huzj | hunan,hubei,jianxi,sichuan    |

| huzy | sichuan,nibo,shanghai,beijing |

+------+-------------------------------+

2 rows in set (0.00 sec)

结论：group_concat()函数可以很好的建属于同一分组的多个行转化为一个列