求统计的SQL语句

[lizhengqin]

Table (有3个字段：a,b,c 都是字符型)

a b c
11,2,4,5 22 11
123,112 11 12
11 12,14 23,11
.............

我想统计某个字符在某列中出现的次数

例如：11 在 a出现2次，在b出现1次，在c出现2次

把统计的字符定义一个变量，要统计哪一列定义一个变量

环境：Sql server 2000

Sql 语句如何写？

[fa_ge]

create table t
(a   varchar(100),  b  varchar(100),  c varchar(10))
insert into t
 
select '11,2,4,5' ,          '22'      ,                   '11'    union all
select '123,112'  ,           '11'     ,                   '12'    union all
select '11'       ,            '12,14' ,                   '23,11' 



select 
	distinct
	(select count(1) from t where charindex(','+'11'+',',','+a+',')>0),
 	(select count(1) from t where charindex(','+'11'+',',','+b+',')>0),
	(select count(1) from t where charindex(','+'11'+',',','+c+',')>0)
	
from t

/*
                                    
----------- ----------- ----------- 
2           1           2

（所影响的行数为 1 行）
*/

[kk19840210]

declare @a varchar(10)
declare @b varchar(10)
declare @sql varchar(1000)

select @a='11'
select @b='b'
select @sql='select count(1) from table1 where charindex('''+@a+''','''+@b+''')>0'
exec @sql

[dawugui]

create table tb(a varchar(20),b  varchar(20),c  varchar(20))
insert into tb values('11,2,4,5','22'   ,'11') 
insert into tb values('123,112' ,'11'   ,'12') 
insert into tb values('11'      ,'12,14','23,11') 
go

select 
(select count(*) from tb where charindex(',11,' , ',' + a + ',') > 0) a,
(select count(*) from tb where charindex(',11,' , ',' + b + ',') > 0) b,
(select count(*) from tb where charindex(',11,' , ',' + c + ',') > 0) c

drop table tb

/*
a           b           c           
----------- ----------- ----------- 
2           1           2

（所影响的行数为 1 行）
*/

[dawugui]

create table tb(a varchar(20),b  varchar(20),c  varchar(20))
insert into tb values('11,2,4,5','22'   ,'11') 
insert into tb values('123,112' ,'11'   ,'12') 
insert into tb values('11'      ,'12,14','23,11') 
go

select id=identity(int , 1 , 1) , * into tmp1 from tb
SELECT TOP 8000 id = identity(int,1,1) INTO tmp2 FROM syscolumns a, syscolumns b   


select isnull(isnull(t1.a,t2.b),t3.c) val , isnull(t1.acnt,0) acnt , isnull(t2.bcnt,0) bcnt , isnull(t3.ccnt,0) ccnt from
(select a , acnt = count(*) from (SELECT A.ID, a = SUBSTRING(A.a, B.ID, CHARINDEX(',', A.a + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.a, B.id, 1) = ',') t group by a) t1 full join
(select b , bcnt = count(*) from (SELECT A.ID, b = SUBSTRING(A.b, B.ID, CHARINDEX(',', A.b + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.b, B.id, 1) = ',') t group by b) t2 on t1.a = t2.b full join
(select c , ccnt = count(*) from (SELECT A.ID, c = SUBSTRING(A.c, B.ID, CHARINDEX(',', A.c + ',', B.ID) - B.ID) FROM tmp1 a, tmp2 b WHERE SUBSTRING(',' + a.c, B.id, 1) = ',') t group by c) t3 on t1.a = t3.c
order by val

GO

drop table tb, tmp1,tmp2

/*
val                  acnt        bcnt        ccnt        
-------------------- ----------- ----------- ----------- 
11                   2           1           2
112                  1           0           0
12                   0           0           1
12                   0           1           0
123                  1           0           0
14                   0           1           0
2                    1           0           0
22                   0           1           0
23                   0           0           1
4                    1           0           0
5                    1           0           0

（所影响的行数为 11 行）
*/

[fa_ge]

create table t
(a   varchar(100),  b  varchar(100),  c varchar(10))
insert into t
 
select '11,2,4,5' ,          '22'      ,                   '11'    union all
select '123,112'  ,           '11'     ,                   '12'    union all
select '11'       ,            '12,14' ,                   '23,11' 


declare @str varchar(100),@field varchar(10),@s varchar(100)
select @str='12'
select @field='c'
select @s=''
select @s=@s+'select count(1)as '''+@field+''' from t where charindex('''+@str+''','+@field+')>0 '
--print @s

exec(@s)

/*
c           
----------- 
1

*/

select 
	distinct
	(select count(1) from t where charindex(','+'11'+',',','+a+',')>0)as a,
 	(select count(1) from t where charindex(','+'11'+',',','+b+',')>0)as b,
	(select count(1) from t where charindex(','+'11'+',',','+c+',')>0)as c
	
from t

/*
a           b           c           
----------- ----------- ----------- 
2           1           2

（所影响的行数为 1 行）
*/

[kk19840210]

declare @a varchar(10)---查询字符
declare @b varchar(10)---查询字段
declare @sql varchar(1000)

select @a='11'
select @b='b'
select @sql='select count(1) from table1 where charindex('',''+'''+@a+'''+'','','',''+'''+@b+'''+'','')>0'
exec @sql

[fymxb]

武林高手！

[lizhengqin]

各位老大,我没有全部测试,测试部分,发现BUG

select count(1) from table where charindex(','+'70'+',',','+a+',')>0

如果a 列中的数据没有逗号会出错
a
11
12
14
44
232
11
70
服务器: 消息 245，级别 16，状态 1，行 3
将 varchar 值 ',' 转换为数据类型为 int 的列时发生语法错误。

如果a 列中任意一条记录有用逗号隔开,则不会报错

哪位老大能否改一下

[dawugui]

Table (有3个字段：a,b,c 都是字符型)

如果你的表是你上面所说的情况,就不会报错.

[lizhengqin]

各位对不起,找到原因 了,因为我的程序中某些列不是字符型 ,是整型,我把它改成字符型就可以了

[kk19840210]

select   count(1)   from   table   where   charindex(','+'70'+',',','+convert(varchar(1000),a)+',')> 0 

[luyesql]

学习

[wangxuelid]

xue xi

[rouqu]

接几分

[fqbnet2050]

都牛人也，我学习了

[microns]

高手！顶一个

[bluedreams85]

mark