背景:查找一个客户对应所有的销售,把所有销售放在同一个单元格中,即将相同的客户对应的销售组合起来。
group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC ] [Separator '分隔符'])
以一个例子来讲解这个函数
select customer,employee
from A
group by customer
如果不加处理,上述代码处理的结果应展示如下:
customer | employee |
---|---|
a | a1 |
a | a2 |
a | a3 |
a | a4 |
a | a5 |
b | b1 |
b | b2 |
c | c1 |
c | c2 |
加上group_concat后,会将同一个客户对应的员工组合在一起,并默认用","相隔(此处用distinct限制排除重复人员)
select customer,group_concat(distinct employee) as emoloyee
from A
group by customer
customer | employee |
---|---|
a | a1,a2,a3,a4,a5 |
b | b1,b2 |
c | c1,c2 |
用separator更改可以更改间隔符号
select customer,group_concat(distinct employee SEPARATOR ';') as emoloyee
from A
group by customer
customer | employee |
---|---|
a | a1;a2;a3;a4;a5 |
b | b1,b2 |
c | c1,c2 |
以上,真省事啊……