34,838
社区成员




select count(1) from table where charindex(','+'70'+',',','+convert(varchar(1000),a)+',')> 0
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
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 行)
*/
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 行)
*/
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 行)
*/
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
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 行)
*/