现有一字段,其内容为:
aaa,bbb,ccc,ddd,........
ddd,fda,dfa,dfdf,......
ddfd,ccd,dfdf,etef,....
.......
n条记录
现要统计出每个字符串出现的次数,也就是aaa,bbb,ccc,.....等出现的次数
aaa,bbb,ccc,ddd,........
ddd,fda,dfa,dfdf,......
ddfd,ccd,dfdf,etef,....
.......
n条记录
现要统计出每个字符串出现的次数,也就是aaa,bbb,ccc,.....等出现的次数
create table a
(
ss varchar(100)
)insert into a select 'aaa,bbb,ccc,ddd'
insert into a select 'ddd,fda,dfa,dfdf'
insert into a select 'ddfd,ccd,dfdf,etef'
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b select ss,count(1) as cou
from
(
SELECT
ss = SUBSTRING(A.ss, B.ID, CHARINDEX(',', A.ss + ',', B.ID) - B.ID)
FROM A, # B
WHERE SUBSTRING(',' + a.ss, B.id, 1) = ','
)tt
group by ss
order by ss
--结果
aaa 1
bbb 1
ccc 1
ccd 1
ddd 2
ddfd 1
dfa 1
dfdf 2
etef 1
fda 1--删除环境
DROP TABLE A
drop table #
union all select '2','ad,ac,ab,ad'
union all select '3','aaa,bbb,c,c'
select aa.d,count(*) as count
from (select id,substring(col2,N,charindex(',',col2 +',',N)-N) as d
from @a a,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11) b
where substring(','+col2,N,1)= ',' ) aa
group by aa.d
/*d count
---------------------------------------- -----------
aaa 2
ab 1
ac 1
ad 2
bbb 2
c 2
ccc 1
*/
create table tb2(id int,discr nvarchar(1000))
insert into tb2 select 1,'aaa,bbb,ccc,ddd,dsa,ewqeqwewq,esdfsd,fertret,fdg4,aaa,fdsf,aaa'
insert into tb2 select 1,'bbb,dsa,yty,ccc,aaa,ttt,bbb,dfd,ccc,aaa'
create proc spselnum
@str varchar(20)='aaa'
as
declare @ind int,@count int ,@col nvarchar(1000)
set @ind=0 set @col='' set @count=0
select @col = @col + discr+',' from tb2while (len(@col)>0) begin
select @ind=charindex(',',@col,1)
if (substring(@col,1,@ind-1)=@str) begin
select @count = @count + 1
select @col=right(@col,len(@col)-@ind)
continue
end
select @col=right(@col,len(@col)-@ind)
end
print '字符"'+@str+'"出现的的次数是:'+ ltrim(@count)