if object_id('A') is not null drop table A
create table A(
论文题目 nvarchar(20),
作者 nvarchar(100)
)
go
insert into A
    select 'A','张三,李四,王五'
union all select 'B','张三,李四'
union all select 'C','李四'
go
if objectproperty(object_id('splitstr'),'IsTableFunction')=1
drop function dbo.splitstr
go
create function dbo.splitstr(@str nvarchar(max),@split varchar(10))
returns @t table(val nvarchar(10))
as
begin    
declare @i int,@s int    
select @i=1,@s=1    
while(@i>0)    
begin            
    set @i=charindex(@split,@str,@s)        
    if(@i>0)       
    begin            
        insert @t(val) values(substring(@str,@s,@i-@s))        
    end           
    else 
    begin            
        insert @t(val) values(substring(@str,@s,len(@str)-@s+1))        
    end        
    set @s = @i + 1       
end    
return
end
go
if objectproperty(object_id('GetSplitResults'),'IsProcedure')=1
drop procedure GetSplitResults
go
create procedure GetSplitResults
as
if object_id('#t') is not null drop table #t
create table #t(
id int identity(1,1),
author nvarchar(20)
)
declare @author nvarchar(100)
declare cur cursor local for select 作者 from A
open cur
fetch next from cur into @author
while @@fetch_status=0
begin
    insert into #t select * from dbo.splitstr(@author,',')
    fetch next from cur into @author
end
close cur
deallocate cur
select 作者名=author,篇数=count(1) from #t group by author order by 篇数 desc
drop table #t
go
exec GetSplitResults