sql语句,帮忙！
现有一字段,其内容为:
aaa,bbb,ccc,ddd,........
ddd,fda,dfa,dfdf,......
ddfd,ccd,dfdf,etef,....
.......
n条记录
现要统计出每个字符串出现的次数,也就是aaa,bbb,ccc,.....等出现的次数
[解决办法]
select *,len(col)-len(replace(col, ', ', ' ')) from tb
[解决办法]
--建表
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
--结果
aaa1
bbb1
ccc1
ccd1
ddd2
ddfd1
dfa1
dfdf2
etef1
fda1
--删除环境
DROP TABLE A
drop table #
[解决办法]
--- 试试看，
declare @a table(id varchar(10),col2 varchar(40))
insert @a select '1 ', 'aaa,bbb,ccc,ddd '
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 tb2
while (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)
