select * from table where name in ('001,002,003,004') , name的值会有一到两个,一个的当然没问题. 但是name='001,003'就不好了name可能是001,可能是002,也可能是003,004,001 这样等
如果一个name中用逗号分割的所有的串都在那个集合中就吧这条记录给取出来否则就排除怎么得到记录集?
如果一个name中用逗号分割的所有的串都在那个集合中就吧这条记录给取出来否则就排除怎么得到记录集?
select * from table where name in ('001','002','003','004')
(id int, name varchar(1000))
insert into tb
select 1 , '001' union all
select 2 , '002' union all
select 3 , '005' union all
select 4 , '004,002' union all
select 5 , '005,001,003' 过滤的条件是 ('001','002','003','004')查询出来的结果是
1 001
2 002
4 004,002=============
以上是希望的效果
select * from @tb
where case when charindex('001',name,1)>0 then len('001') else 0 end
+case when charindex('002',name,1)>0 then len('002') else 0 end
+case when charindex('003',name,1)>0 then len('003') else 0 end
+case when charindex('004',name,1)>0 then len('004') else 0 end=len(replace(replace(name,',',''),' ','')),如其它情况,应动态生成其它其它查询条件.如'0001,0005'
就生成
select * form @tb
where
case when charindex('001',name,1)>0 then len('001') else 0 end
+case when charindex('004',name,1)>0 then len('005') else 0 end=len(replace(replace(name,',',''),' ',''))
注意其中','的逗号,全角与半角要不能搞混,搞混就不准.
上面查询只适用于串中无重复,如'001,001,002'这样的情况就不能正确查出
select * from table where charindex(name,'001,002,003,004')!=0
set @in='001,002,003,004'
declare @sql varchar(1000)
select @sql=' insert @a select '''+replace(@in,',',''' union select ''')+''' '
select @sql='declare @a table (a varchar(3)) '+@sql+' select id,name from tb a left join @a b on charindex(b.a,a.name)>0 group by id,name having count(a)=1+len(name)-len(replace(name,'','','''')) '
print (@sql)
------------------------
1 001
2 002
4 004,002
---------------------------
--select id,name from tb a left join (数据集形式) b on charindex(b.a,a.name)>0 group by id,name having count(a)=1+len(name)-len(replace(name,',',''))
select * from @tb
where replace(replace(replace(replace(replace(replace(','+name+',',' ',''),--去空格
',001,',',,'), --去掉001
',002,',',,'), --去掉002
',003,',',,'), --去掉003
',004,',',,'),--最后去掉逗号
',','')
='' --等于空格时即为符合条件语句都是要用动态生成,生成动态查询语句时注意引号
select * from 表
where
[name] like '%001%'
or
[name] like '%002%'
or
[name] like '%003%'
or
[name] like '%003%'
set @Filter='001,002,003,004'
select
*
from 表
where charindex(','+name+',',','+@Filter+',")<>0