高分在线求:如何用SQL统计行中的重复内容
比如如下表:
论文题目 作者
A 张三,李四,王五
B 张三,李四
C 李四
利用SQL语句输出如下结构:
作者名 篇数
李四 3
张三 2
王五 1
[解决办法]
if object_id('A') is not null drop table Acreate table A(论文题目 nvarchar(20),作者 nvarchar(100))goinsert into A select 'A','张三,李四,王五'union all select 'B','张三,李四'union all select 'C','李四'goif objectproperty(object_id('splitstr'),'IsTableFunction')=1drop function dbo.splitstrgocreate function dbo.splitstr(@str nvarchar(max),@split varchar(10))returns @t table(val nvarchar(10))asbegin 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 returnendgoif objectproperty(object_id('GetSplitResults'),'IsProcedure')=1drop procedure GetSplitResultsgocreate procedure GetSplitResultsasif object_id('#t') is not null drop table #tcreate table #t(id int identity(1,1),author nvarchar(20))declare @author nvarchar(100)declare cur cursor local for select 作者 from Aopen curfetch next from cur into @authorwhile @@fetch_status=0begin insert into #t select * from dbo.splitstr(@author,',') fetch next from cur into @authorendclose curdeallocate curselect 作者名=author,篇数=count(1) from #t group by author order by 篇数 descdrop table #tgoexec GetSplitResults