数据库如何处理拆分字段内带逗号拼接及用逗号合并同一字段内的值
项目场景:同一字段内存在用逗号拼接的如何拆分
提示:同一字段内存在用逗号拼接的的如何拆分
例如:项目场景:把id:1;names:aaa,bbb,ccc这种拆分成 id:1;names:aaa; id:2;names:bbb;id:3;names:ccc;
问题描述:想把上述的names字段拆分,分成三条记录
提示:想把上述的names字段拆分,分成三条记录。
解决方案:SQL实现
提示:直接上SQL语句
SELECT
b.names
FROM
(
SELECT
names = CONVERT(
xml,
'<root><v>'+REPLACE(names,',','</v><v>')+''</v></root>'
)
FROM 表
)a OUTER APPLY (
SELECT
names C.v.value
('.','nvarchar(100)')
FROM
a.names.nodes ('/root/v') C (v)
)b
SELECT
names = CONVERT(
xml,
‘’+REPLACE(names,’,’,’’)+’’’
)
FROM 表
这部分直接把names转成xml格式:aaabbbccc
项目场景:同一字段内存在多个值如何用逗号拼接
提示:同一字段内存在多个值如何用逗号拼接:
例如:项目场景:把 id:1;names:aaa; id:2;names:bbb;id:3;names:ccc这种合并成id:1;names:aaa,bbb,ccc
问题描述:想把上述的三条names字段合并成一条
提示:想把上述的三条names字段合并成一条:
解决方案:SQL实现(SQLserver、MySQL)
一、:SQLserver解决方式用xml for path:
首先呢!我们在增加一张学生表,列分别为(stuID,sName,hobby),stuID代表学生编号,sName代表学生姓名,hobby列存学生的爱好!那么现在表结构如下:
这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:
SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B
结果如下:
二、:MySQL解决方式用group_concat函数:
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
下面举例说明:
select id,price from goods;
以id分组,把price字段的值在同一行打印出来,逗号分隔(默认)
select id, group_concat(price) from goods group by id;
以id分组,把price字段的值在一行打印出来,分号分隔
select id,group_concat(price separator ';') from goods group by id;
以id分组,把去除重复冗余的price字段的值打印在一行,逗号分隔
select id,group_concat(distinct price) from goods group by id;
以id分组,把price字段的值去重打印在一行,逗号分隔,按照price倒序排列
select id,group_concat(DISTINCT price order by price desc) from goods group by id;