将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
group_concat函数首先根据group by指定的列进行分组,将同一组的列显示出来,并且用分隔符分隔。由函数参数(字段名)决定要返回的列。
用法:
(1)使用distinct可以排除重复值;
(2)如果需要对结果中的值进行排序,可以使用order by子句;
(3)separator是一个字符串值,默认为逗号。
# group_concat可以
select name,group_concat(age) from emp group by dept;
# 例如
mysql> select * from t1; # 表中数据
+----+-------+-----------+---------+
| id | name | dept | job |
+----+-------+-----------+---------+
| 1 | jack | 销售类 | sale |
| 2 | rose | 销售类 | sale |
| 3 | tom | 技术类 | DBA |
| 4 | jerry | 技术类 | develop |
| 5 | kevin | 技术类 | develop |
+----+-------+-----------+---------+
5 rows in set (0.00 sec)
# 可以将单列输出
mysql> select job,group_concat(name) from t1 group by job;
+---------+--------------------+
| job | group_concat(name) |
+---------+--------------------+
| DBA | tom |
| develop | jerry,kevin |
| sale | jack,rose |
+---------+--------------------+
3 rows in set (0.00 sec)
# 也可以对多列使用
mysql> select job,group_concat(name,'|',dept) from t1 group by job;
+---------+---------------------------------+
| job | group_concat(name,'|',dept) |
+---------+---------------------------------+
| DBA | tom|技术类 |
| develop | jerry|技术类,kevin|技术类 |
| sale | jack|销售类,rose|销售类 |
+---------+---------------------------------+
3 rows in set (0.00 sec)
# 可以指定分隔符号,使用separator即可
mysql> select job,group_concat(name separator ';') from t1 group by job;
+---------+----------------------------------+
| job | group_concat(name separator ';') |
+---------+----------------------------------+
| DBA | tom |
| develop | jerry;kevin |
| sale | jack;rose |
+---------+----------------------------------+
3 rows in set (0.00 sec)
# 使用order by进行排序
mysql> select job,group_concat(name, dept order by name separator ';') from t1 group by job;
+---------+------------------------------------------------------+
| job | group_concat(name, dept order by name separator ';') |
+---------+------------------------------------------------------+
| DBA | tom技术类 |
| develop | jerry技术类;kevin技术类 |
| sale | jack销售类;rose销售类 |
+---------+------------------------------------------------------+
3 rows in set (0.00 sec)
标签:group,name,job,技术类,MySQL,concat,select
From: https://www.cnblogs.com/smyz/p/17010121.html