22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(字段1 varchar(10),字段2 int)
insert into tb
select 'a',1 union all
select 'b,a',2 union all
select 'a,c',1 union all
select 'c,b',1 union all
select 'd',3
;with maco as
(
SELECT A.字段2, B.value as 字段1 FROM( SELECT 字段2,
[value] = CONVERT(xml,'<root><v>' + REPLACE(字段1, ',', '</v><v>') + '</v></root>') FROM tb
)A OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v))B
)
select 字段1,sum(字段2) as 数量 from maco group by 字段1
/*
字段1 数量
-------- -----------
a 4
b 3
c 2
d 3
*/
declare @s nvarchar(max)
set @s=(select ','+col1 from tb for xml path(''))
select col[字段1],count(1)数量 from(
select col=(case when stuff(@s,1,number,'') like'%,%' then
left(stuff(@s,1,number,''),charindex(',',stuff(@s,1,number,''))-1) else stuff(@s,1,number,'') end)
from master..spt_values where type='p' and number<len(@s) and substring(@s,number,1)=',')t
group by col