create tmpTbl ( keywords varchar(50))
declare @s varchar(8000)
set @s=''
select @s=@s + 'union all select ' + replace(keywords,';',' union all select ') from tbl1
set @s=substring( @s, 10,8000)
exec('insert tmptbl '+ @s )
select keywords, count(*) as [count] into tbl2 group by keywords
drop table tmptbl
写的不是很清楚。
是想统计KEYWORDS里每个关键字出现的个数吗?
如果是可以另设一个表:
如下:
列1 列2
1 kw1
1 kw2
1 kw3
2 kw2
2 kw3
3 kw3
3 kw1
3 kw4
不能重复,是指什么?某一个怎么解释?
-----------------------------------------------------------
1 kw1;kw2;kw3
2 kw2;kw3
3 kw3;kw1;kw4
以上是原始表,在keywords字段中出现了kw1,kw2,kw3,kw4对不对?
简单的说,我的意思要统计没个关键字(上例中关键字全集为:{kw1;kw2;kw3;kw4}出现的次数。对于上例,要得到结论如下:Keywords KeyCount
kw1 2
kw2 2
kw3 3
kw4 1
Declare @keystr varchar(50),
@in1 int,
@in2 int,
@len intSet @len=0create table #temp(keywords varchar(10))declare cursorname cursor for
Select keywords from tb
order by idopen cursorname
fetch next From cursorname into @keystr
while @@Fetch_status=0
begin
select @in1=1,@in2=0
set @len=len(@keyStr)
while @in2<=@len
begin
set @in2=charindex(';',@keystr,@in1)
if @in2>0
begin
Insert #temp
select substring(@keystr,@in1,@in2-@in1)
set @in1=@in2+1
end
else
begin
If @in1<@len
begin
Insert #temp
select substring(@keystr,@in1,@len-@in1+1)
end
set @in2=@len+1
end
end
fetch next From cursorname into @keystr
end
close cursorname
deallocate cursorname select keywords,count(keywords) as keyCount
from #temp
group by keywords
order by keywords drop table #temp