Question

SQLServer差费逗号分隔的字段并统计

有个表中的一个字段Author，如下
ID Author
1 张三
2 张三,李四
3 王五
4 李四
5 张三,李四,王五

现在想查询出这样的结果
Author Count
张三 3
李四 3
王五 2

求高手啊，实在没有分了

Answer 1

create table author(
id int identity,
author nvarchar(20)
)

insert into author values('张三')
insert into author values('张三,李四')
insert into author values('王五')
insert into author values('李四')
insert into author values('张三,李四,王五')

select * from author

select OneTitle,COUNT(OneTitle) as count
from
(
select id,
Author=convert(xml, '<root><v>' + replace(Author,',','</v><v>') + '</v></root>')
from Author
)a
outer apply
(
select OneTitle = C.v.value('.', 'nvarchar(100)')
from a.Author.nodes('/root/v')C(v)
) b
group by OneTitle order by count desc

Answer 2

if object_id('Tempdb..#Num') is not null
drop table #Num
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
Author=substring(a.Author,b.ID,charindex(',',a.Author+',',b.ID)-b.ID),count(*)
from
table1 a,#Num b
where
charindex(',',','+a.Author,b.ID)=b.ID
group by substring(a.Author,b.ID,charindex(',',a.Author+',',b.ID)-b.ID);

table1替换成你自己表名，top 100 那个100你就替换个稍微大点的数吧