【MySQL】字符串分组连接group_concat()
将字符分组后连接在一起。
1：语法：
|
1 |
group_concat([DISTINCT] colname [Order BY ASC/DESC colname] [Separator '分隔符']) |
2：举例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
## 创建测试表 mysql> create table t(deptno int(4),name varchar(10)); mysql> insert into t values(1,'zhangsan'),(1,'lisi'),(1,'wangwu'); mysql> insert into t values(2,'zhaoliu'),(2,'zhuqi'); mysql> insert into t values(3,'liubei'),(3,'guanyu'),(3,'zhangfei'),(3,'zhaoyun'); mysql> mysql> select * from t; +--------+----------+ | deptno | name | +--------+----------+ | 1 | zhangsan | | 1 | lisi | | 1 | wangwu | | 2 | zhaoliu | | 2 | zhuqi | | 3 | liubei | | 3 | guanyu | | 3 | zhangfei | | 3 | zhaoyun | +--------+----------+ 9 rows in set (0.00 sec) mysql> ## 分组连接 mysql> select deptno,group_concat(name) from t group by deptno; +--------+--------------------------------+ | deptno | group_concat(name) | +--------+--------------------------------+ | 1 | zhangsan,lisi,wangwu | | 2 | zhaoliu,zhuqi | | 3 | liubei,guanyu,zhangfei,zhaoyun | +--------+--------------------------------+ 3 rows in set (0.00 sec) mysql> ## 指定分隔符 mysql> select deptno,group_concat(name separator ';') from t group by deptno; +--------+----------------------------------+ | deptno | group_concat(name separator ';') | +--------+----------------------------------+ | 1 | zhangsan;lisi;wangwu | | 2 | zhaoliu;zhuqi | | 3 | liubei;guanyu;zhangfei;zhaoyun | +--------+----------------------------------+ 3 rows in set (0.02 sec) mysql> ## 字符串排序后再进行连接 mysql> select deptno,group_concat(name order by name desc) from t group by deptno; +--------+---------------------------------------+ | deptno | group_concat(name order by name desc) | +--------+---------------------------------------+ | 1 | zhangsan,wangwu,lisi | | 2 | zhuqi,zhaoliu | | 3 | zhaoyun,zhangfei,liubei,guanyu | +--------+---------------------------------------+ 3 rows in set (0.00 sec) mysql> |
3：Oracle类似功能的函数
这个函数跟Oracle的wm_concat函数有点儿类似，但比Oracle的功能更全一些。Oracle的wm_concat函数功能如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
## 印象中10g才有这个函数，但未公开，11g开始可以公开使用。 ## 当然，在没有这个函数之前，Oracle有很多办法实现这个效果，这里就不举例子了 SQL> conn scott/tiger 已连接。 SQL> select deptno,wm_concat(ename) as name from emp group by deptno; DEPTNO NAME ---------- -------------------------------------------------- 10 CLARK,MILLER,KING 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD SQL> |