--sql 2000create table tb(proID varchar(10), result varchar(50))
insert into tb values('A' , '人为,设备,材料,其他')
insert into tb values('B' , '人为,设备')
insert into tb values('C' , '其他')
insert into tb values('D' , '设备,材料')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select result , count(1) cnt from
(
SELECT A.proID, result = SUBSTRING(A.[result], B.id, CHARINDEX(',', A.[result] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[result], B.id, 1) = ','
) m
group by resultDROP TABLE #drop table tb /*
result cnt
-------------------------------------------------- -----------
设备 3
其他 2
人为 2
材料 2(所影响的行数为 4 行)*/
insert into tb values('A' , '人为,设备,材料,其他')
insert into tb values('B' , '人为,设备')
insert into tb values('C' , '其他')
insert into tb values('D' , '设备,材料')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select result , count(1) cnt from
(
SELECT A.proID, result = SUBSTRING(A.[result], B.id, CHARINDEX(',', A.[result] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[result], B.id, 1) = ','
) m
group by resultDROP TABLE #drop table tb /*
result cnt
-------------------------------------------------- -----------
设备 3
其他 2
人为 2
材料 2(所影响的行数为 4 行)*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
insert into tb values('A' , N'人为,设备,材料,其他')
insert into tb values('B' , N'人为,设备')
insert into tb values('C' , N'其他')
insert into tb values('D' , N'设备,材料')
go
select result , count(1) cnt from
(
SELECT A.proID, B.result
FROM(
SELECT proID, [result] = CONVERT(xml,'<root><v>' + REPLACE([result], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT result = N.v.value('.', 'nvarchar(100)') FROM A.[result].nodes('/root/v') N(v)
)B
) m
group by resultdrop table tb /*
result cnt
---------------------------------------------------------------------------------------------------- -----------
人为 2
其他 2
材料 2
设备 3(4 行受影响)*/