Mysql数据库中字符串连接有3个常用函数:CONCAT、CONCAT_WS和GROUP_CONCAT,其中CONCAT、CONCAT_WS类似,而GROUP_CONCAT顾名思义是一个集合操作函数,本文谈谈三者区别及使用场景,同时分享一个Demo,用GROUP_CONCAT如何实现将细表物料名称汇总到主表物料详情。
1、CONCAT()
注意:returns NULL if any argument is NULL.
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL
mysql> SELECT CONCAT('My', '', 'SQL'); -> 'MySQL'
mysql> SELECT CONCAT(14.3); -> '14.3'
2、CONCAT_WS(separator,str1,str2,…)
我们可以简单理解:CONCAT = CONCAT_WS(’’, str1, str2, …),但两者又有差别,主要在null参数处理上。
- CONCAT函数,只要参数有null,则返回null
- CONCAT_WS,参数有null,则会跳过null,继续连接下个参数,但是分隔符若为null,则返回null
mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name'); -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name'); -> 'First name,Last Name'
mysql> SELECT CONCAT_WS(null,'First name',NULL,'Last Name'); -> NULL
mysql> SELECT CONCAT_WS(null,'First name','Last Name'); -> NULL
mysql> SELECT CONCAT_WS(',',null,null) = ''; -> 1, true
3、GROUP_CONCAT(expr)
GROUP_CONCAT(expr),返回符合要求结果集的字符串拼接,返回字符串有几个特点:
- 默认逗号分隔
- 默认长度最大 1024,超过部分自动截断,可通过SET [GLOBAL | SESSION] group_concat_max_len = val修改。
- NULL值跳过
GROUP_CONCAT(expr) 语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
GROUP_CONCAT,具体如何使用,看一个Demo:如何将细表物料名称汇总到主表物料详情。
4、Demo讲解
1)准备2张表:采购单主表,采购单细表
**采购单主表,pur_po_bill **
sid | goods_name |
---|---|
323 | 奶嘴黄色PP礼袋条码,营养餐208套标 |
324 | 小奶嘴盖_Yellow C |
325 | 奶嘴黄色PP礼袋条码,浓缩番茄汁 |
**采购单细表,pur_po_bill_detail **
sid | bill_id | goods_name | memo |
---|---|---|---|
1 | 323 | 奶嘴黄色PP礼袋条码 | |
2 | 323 | 营养餐208套标 | |
3 | 323 | 营养餐208套标 | 实际情况物料不能重复,为了 函数说明需要特意修改 |
4 | 324 | 小奶嘴盖_Yellow C | |
4 | 325 | 奶嘴黄色PP礼袋条码 | |
5 | 325 | 浓缩番茄汁 |
GROUP_CONCAT 演示1
SELECT bill_id, GROUP_CONCAT(goods_name)
FROM pur_po_bill_detail
WHERE bill_id in(323,324,325)
GROUP BY bill_id;
sql输出结果:
bill_id | GROUP_CONCAT(goods_name) |
---|---|
323 | 奶嘴黄色PP礼袋条码,营养餐208套标,营养餐208套标 |
324 | 小奶嘴盖_Yellow C |
325 | 奶嘴黄色PP礼袋条码,浓缩番茄汁 |
GROUP_CONCAT 演示2
SELECT bill_id, GROUP_CONCAT(DISTINCT goods_name order by CONVERT(goods_name USING gbk) asc SEPARATOR '@')
FROM pur_po_bill_detail
WHERE bill_id in(323,324,325)
GROUP BY bill_id;
sql输出结果:
bill_id | GROUP_CONCAT(goods_name) |
---|---|
323 | 奶嘴黄色PP礼袋条码@营养餐208套标 |
324 | 小奶嘴盖_Yellow C |
325 | 奶嘴黄色PP礼袋条码@浓缩番茄汁 |
sql 说明:
1)指定了分隔符,SEPARATOR ‘@’
2)指定了排序
3)DISTINCT goods_name 区重处理
GROUP_CONCAT 演示3
将细表物料名称,更新到主表物料名称。
update pur_po_bill a
set a.goods_name = (select GROUP_CONCAT(goods_name)
from pur_po_bill_detail b where b.bill_id = a.sid)
where a.sid in (323,324,325)
sql 结果见 采购单主表pur_po_bill数据。