行转列group_concat

行转列group_concat,第1张

行转列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()函数可以很好的建属于同一分组的多个行转化为一个列

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/586715.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-04-12
下一篇 2022-04-12

发表评论

登录后才能评论

评论列表(0条)

保存