数据表:
Id NM
1 苹果,梨,橘子,香蕉,
2 苹果,橘子,
3 香蕉,
-------------------------------------------------------------
最后的统计激活我想要的是 NM Number
苹果 2
梨 1
橘子 2
香蕉 2
-------------------------------------------------------------------
谢谢大家的帮忙....
Id NM
1 苹果,梨,橘子,香蕉,
2 苹果,橘子,
3 香蕉,
-------------------------------------------------------------
最后的统计激活我想要的是 NM Number
苹果 2
梨 1
橘子 2
香蕉 2
-------------------------------------------------------------------
谢谢大家的帮忙....
insert into tb select 1,'苹果,梨,橘子,香蕉,'
insert into tb select 2,'苹果,橘子,'
insert into tb select 3,'香蕉'
go
--把各种逗号统一成相同的逗号
update tb set NM=replace(NM,',',',')
--统一成后面没有逗号
update tb set NM=left(NM,len(NM)-1) where right(NM,1)=','
go
select NM,count(*)ct from(
select a.id,b.NM from(
select id,NM = CONVERT(xml,'<root><v>' + REPLACE(NM, ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT NM = N.v.value('.', 'varchar(100)') FROM A.NM.nodes('/root/v') N(v)
)B
)C group by NM
go
drop table tb
/*
NM ct
---------------------------------------------------------------------------------------------------- -----------
橘子 2
梨 1
苹果 2
香蕉 2(4 行受影响)*/
use tempdb;
/*
create table tb
(
id int not null,
NM nvarchar(10) not null
);
insert into tb(id,NM)
values
(1,'苹果,梨,橘子,香蕉'),
(2,'苹果,橘子'),
(3,'香蕉');
*/
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT t.NM,COUNT(t.NM) as [Number]
FROM
(
SELECT A.id, SUBSTRING(A.[NM], B.id, CHARINDEX(',', A.[NM] + ',', B.id) - B.id) as [NM]
FROM tb A, # B
WHERE SUBSTRING(',' + A.[NM], B.id, 1) = ','
) as t
group by t.NM;