请求sql大神们帮帮我啊

[mrchuei]

现在有一张表名叫 表Article
有字段 id,title,keyword,content
现在keyword里面的数据是这样的
"苹果,ipad,google"
"三国杀,ipad,苹果,qq"
"苹果,三国杀,qq"
"iphone,三国杀,qq"
类似这样的数据有很多行
现在前台需要根据keyword这个关键词以","分割来检索出这些关键词，
最后出现的结果应该是“苹果,三国杀,ipad,qq,google,iphone" 这样的 并且按照出现次数最多的排在最前面
请各位大虾们帮帮我 这是要做热门关键词的检索功能

[PoorScholor1st]

引用 2 楼 usbusb2007 的回复:

动态的 create table fenge ( id int identity(1,1), name varchar(255) ) declare @t int set @t=select max(id) from Article declare @q int set @q=1 while @q<@t declare @i varchar(255) set @i=(select keyword from where id =@q if(charindex(',',@i)=0) and (len(@i))>=1 begin insert into fenge (name) values (@i) end while charindex(',',@i)>0 begin declare @j int set @j=charindex(',',@i) insert into fenge (name) values (substring(@i,0,@j)) set @i=substring(@i,@j+1,len(@i)) if(charindex(',',@i)=0) and (len(@i))>=1 begin insert into fenge (name) values (@i) end end set @q=@q+1 end select count(*) c,name from fenge group by name order by c

稍做修改： if OBJECT_ID('fenge') is not null drop table fenge create table fenge ( id int identity(1,1), name varchar(255) ) declare @t int set @t=(select max(id) from Article ) declare @q int set @q=1 while @q<=@t begin declare @i varchar(255) set @i=(select keyword from Article where id =@q) while charindex(',',@i)>0 begin declare @j int set @j=charindex(',',@i) insert into fenge (name) values (substring(@i,0,@j)) set @i=substring(@i,@j+1,len(@i)) if(charindex(',',@i)=0) and (len(@i))>=1 begin insert into fenge (name) values (@i) end end set @q=@q+1 end select * from Article select * from fenge select count(*) c,name from fenge group by name order by c desc

[哥眼神纯洁不]

with tb(keyword) as(
select '苹果,ipad,google' union all
select '三国杀,ipad,苹果,qq' union all
select '苹果,三国杀,qq' union all
select 'iphone,三国杀,qq' ),tc as(
select a=substring(keyword,number,CHARINDEX(',',keyword+',',number+1)-number),
b=COUNT(1) from tb,master..spt_values where type='p' 
and SUBSTRING(','+keyword,number,1)=','
group by substring(keyword,number,CHARINDEX(',',keyword+',',number+1)-number))
select a=stuff((select ','+a from tc  order by b desc for xml path('')),1,1,'')

[usbusb2007]

动态的 create table fenge ( id int identity(1,1), name varchar(255) ) declare @t int set @t=select max(id) from Article declare @q int set @q=1 while @q<@t declare @i varchar(255) set @i=(select keyword from where id =@q if(charindex(',',@i)=0) and (len(@i))>=1 begin insert into fenge (name) values (@i) end while charindex(',',@i)>0 begin declare @j int set @j=charindex(',',@i) insert into fenge (name) values (substring(@i,0,@j)) set @i=substring(@i,@j+1,len(@i)) if(charindex(',',@i)=0) and (len(@i))>=1 begin insert into fenge (name) values (@i) end end set @q=@q+1 end select count(*) c,name from fenge group by name order by c