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

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, '' + replace(Author,',','') + '')
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

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你就替换个稍微大点的数吧