1、利用for xml path函数拼接字符串 2、在用substr截取计算 可以参考我的博客: http://blog.csdn.net/a275838263/article/details/51118866
with a(country,num) as( select '肯尼亚,莫桑比克,苏丹,加蓬',1 union all select '肯尼亚、莫桑比克',1 union all select '肯尼亚、莫桑比克、苏丹',1)select c.country,sum(b.num) as num from (select convert(xml,'<n><v>'+ replace(REPLACE(a.country,'、','</v><v>'),',','</v><v>')+'</v></n>') as pi,num from a) as b outer apply(select v.s.value('.','varchar(500)') as country from b.pi.nodes('/n/v') as v(s)) as c group by c.country/* country num ------------------ 加蓬 1 肯尼亚 3 莫桑比克 3 苏丹 2 ----------------- */
http://blog.csdn.net/tastelife/article/details/7914646
2、在用substr截取计算
可以参考我的博客:
http://blog.csdn.net/a275838263/article/details/51118866
select '肯尼亚,莫桑比克,苏丹,加蓬',1 union all
select '肯尼亚、莫桑比克',1 union all
select '肯尼亚、莫桑比克、苏丹',1)select c.country,sum(b.num) as num from
(select convert(xml,'<n><v>'+
replace(REPLACE(a.country,'、','</v><v>'),',','</v><v>')+'</v></n>') as pi,num from a) as b
outer apply(select v.s.value('.','varchar(500)') as country from b.pi.nodes('/n/v') as v(s)) as c
group by c.country/*
country num
------------------
加蓬 1
肯尼亚 3
莫桑比克 3
苏丹 2
-----------------
*/