某字符出现的次数统计

[我辣椒哥]

 
表test 有字段 A B C
数据如下

     A -----B----- C
1,22,3-----5,8,69-----21
22,23-----1,18,169-----21,10
20,23-----13,10,169-----10,12,21

现在统计字段A 22出现次数应该是2次 23出现的次数是2次
...
现在统计字段C 21出现的次数是3次

SQL语句应该怎么写呢？

[王向飞]

select (len(cole1)-replace(cole1,'22','') )/2

[zheninchangjiang]

select count(*) from tablename where charindex(c,'21')>=0

[我辣椒哥]

[Quote=引用 1 楼 wxf163 的回复:]
SQL code
select (len(cole1)-replace(cole1,'22','') )/2
[/Quote]
替换为空？

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-12 22:05:09
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([A] varchar(6),[B] varchar(9),[C] varchar(8))
insert [tb]
select '1,22,3','5,8,69','21' union all
select '22,23','1,18,169','21,10' union all
select '20,23','13,10,169','10,12,21'
--------------开始查询--------------------------
select
    a,COUNT(1) as 次数
from
(
Select
    a=substring(a.a,b.number,charindex(',',a.a+',',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.b)
where
     substring(','+a.a,b.number,1)=',')t
group by
   a
----------------结果----------------------------
/* a      次数
------ -----------
1      1
20     1
22     2
23     2
3      1

(5 行受影响)
*/

[rucypli]

[Quote=引用 3 楼 tianmeshi 的回复:]
引用 1 楼 wxf163 的回复:
SQL code
select (len(cole1)-replace(cole1,'22','') )/2

替换为空？
[/Quote]
对

[快溜]

[Quote=引用 1 楼 wxf163 的回复:]
SQL code
select (len(cole1)-replace(cole1,'22','') )/2
[/Quote]有风险，如果查b列69就出错了，还是f美女的方法吧。

[dawugui]

应该用四楼小F姐姐的先拆分然后再分组统计.

[一直学习]

--准备
create table test (A varchar(50), B varchar(50), C varchar(50))
insert into test select '1,22,3','5,8,69','21'
union all select '22,23','1,18,169','21,10'
union all select '20,23','3,10,169','10,12,21'
select * from test

--SQL
declare @sqlA varchar(500)
declare @sqlB varchar(500)
declare @sqlC varchar(500)
set @sqlA = '' set @sqlB='' set @sqlC=''
select @sqlA=@sqlA+A+',', @sqlB=@sqlB+B+',',@sqlC=@sqlC+C+',' from test
declare @tt table(id varchar(50),col varchar(100))
insert into @tt select 'A',left(@sqlA,len(@sqlA)-1)
union all select  'B',left(@sqlB,len(@sqlB)-1)
union all select  'C',left(@sqlC,len(@sqlC)-1)
--select * from @tt
;with cr as(
select a.id, col = substring(a.col, b.number , charindex(',' , a.col+ ',' , b.number) - b.number) 
from @tt a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.col)
where substring(',' + a.col, b.number , 1) = ',')

select id as [所在列],col as [统计值],count(*) as [出现次数]  from cr group by id,col order by id,col

/*
所在列  统计值   出现次数
------------------
A	1	1
A	20	1
A	22	2
A	23	2
A	3	1
B	1	1
B	10	1
B	169	2
B	18	1
B	3	1
B	5	1
B	69	1
B	8	1
C	10	2
C	12	1
C	21	3
*/

[renkai721]

select count(*) as 'c出现的次数' from test
where c=21
行不？

[-晴天]

create table test(A nvarchar(20),B nvarchar(20),C nvarchar(20))
insert into test select '1,22,3','5,8,69','21'
insert into test select '22,23','1,18,169','21,10'
insert into test select '20,23','13,10,169','10,12,21'
go
select count(*) from test where charindex(',22,',','+A+',')>0
/*
-----------
2

(1 行受影响)
*/
select count(*) from test where charindex(',23,',','+A+',')>0
/*
-----------
2

(1 行受影响)
*/
select count(*) from test where charindex(',21,',','+C+',')>0
-----------
3

(1 行受影响)

*/
go
drop table test

[我辣椒哥]

[Quote=引用 12 楼 qianjin036a 的回复:]
抱歉,写错一个字:

SQL code
create table test(A nvarchar(20),B nvarchar(20),C nvarchar(20))
insert into test select '1,22,3','5,8,69','21'
insert into test select '22,23','1,18,169','21,10'
insert into ……
[/Quote]
如何数据里面包含了中文字符，这方法好像不行，小F，好像可以

[-晴天]

要全查出来么?
那:

create table test(A nvarchar(20),B nvarchar(20),C nvarchar(20))
insert into test select '1,22,3','5,8,69','21'
insert into test select '22,23','1,18,169','21,10'
insert into test select '20,23','13,10,169','10,12,21'
go
--设一个数在同一数列中只出现一次,且想统计的数在200以内
select 统计,sum(A) A,sum(B) B,sum(C) C from(
select a.number as 统计,
(case when charindex(','+ltrim(a.number)+',',','+b.A+',')>0 then 1 else 0 end) A,
(case when charindex(','+ltrim(a.number)+',',','+b.B+',')>0 then 1 else 0 end) B,
(case when charindex(','+ltrim(a.number)+',',','+b.B+',')>0 then 1 else 0 end) C
 from master..spt_values a,test b where a.type='p' and a.number<=200
)t group by 统计 having sum(A)>0 or sum(B)>0 or sum(C)>0
/*
统计          A           B           C
----------- ----------- ----------- -----------
1           1           1           1
3           1           0           0
5           0           1           1
8           0           1           1
10          0           1           1
13          0           1           1
18          0           1           1
20          1           0           0
22          2           0           0
23          2           0           0
69          0           1           1
169         0           2           2

(12 行受影响)

*/
go
drop table test

[-晴天]

抱歉,写错一个字:

create table test(A nvarchar(20),B nvarchar(20),C nvarchar(20))
insert into test select '1,22,3','5,8,69','21'
insert into test select '22,23','1,18,169','21,10'
insert into test select '20,23','13,10,169','10,12,21'
go
--设一个数在同一数列中只出现一次,且想统计的数在200以内
select 统计,sum(A) A,sum(B) B,sum(C) C from(
select a.number as 统计,
(case when charindex(','+ltrim(a.number)+',',','+b.A+',')>0 then 1 else 0 end) A,
(case when charindex(','+ltrim(a.number)+',',','+b.B+',')>0 then 1 else 0 end) B,
(case when charindex(','+ltrim(a.number)+',',','+b.C+',')>0 then 1 else 0 end) C
 from master..spt_values a,test b where a.type='p' and a.number<=200
)t group by 统计 having sum(A)>0 or sum(B)>0 or sum(C)>0
/*
统计          A           B           C
----------- ----------- ----------- -----------
1           1           1           0
3           1           0           0
5           0           1           0
8           0           1           0
10          0           1           2
12          0           0           1
13          0           1           0
18          0           1           0
20          1           0           0
21          0           0           3
22          2           0           0
23          2           0           0
69          0           1           0
169         0           2           0

(14 行受影响)
*/
go
drop table test