表a:
a1 | a2
1 | 1,2
2 | 2,5
3 | 3,5
4 | 2,7
5 | 1,2,8
现在我想查select * from a where a2 in (1,2,5);出错!求正确写法
a1 | a2
1 | 1,2
2 | 2,5
3 | 3,5
4 | 2,7
5 | 1,2,8
现在我想查select * from a where a2 in (1,2,5);出错!求正确写法
调试欢乐多
where charindex( ',1,',','+a2+',')
or charindex( ',2,',','+a2+',')
or charindex( ',5,',','+a2+',')
where charindex( ',1,',','+a2+',') >0
and id in (select id from a where charindex( ',2,',','+a2+',')>0)
and id in (select id from a where charindex( ',5,',','+a2+',')>0)
set @s = '1,2,5'
select * from a where charindex(','+a2+',',','+@s+',',1)>0
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (a1 int,a2 varchar(20))
insert into #tb
select 1,'1,2' union all
select 2,'2,5' union all
select 3,'3,5' union all
select 4,'2,7' union all
select 5,'1,2,8'
select a1,a2=substring(a2, number,charindex(',',a2+',',number)-number)
into #
from #tb a,master..spt_values s
where type='p' and substring(','+a2,number,1)=','select distinct b.*
from # t ,#tb b
where not exists (select 1 from # where a1=t.a1 and charindex(','+a2+',',',1,2,5,')=0)
and b.a1=t.a1
a1 a2
----------- --------------------
1 1,2
2 2,5(2 行受影响)
drop table #
原理是这样的
1,2先变成--->,1,2,
1,2,5先变成---->,1,2,5,然后判断",1,2,"是否包含在",1,2,5,"中,您说是否包含?这很明显啊!对吧?
like 也可以吧?模糊匹配
a1 | a2
1 | 1,2
2 | 2,5
3 | 3,5
4 | 2,17
5 | 1,2,8
6 | 1,5
7 | 2
8 | 1,2,5
现在我想查select * from a where a2 in (1,2,5);出错!求正确写法
我想查出(1,2),(2,5),(1,5),(2),(1,2,5)
这个结果有多少分if object_id('tb01') is not null drop table tb01
gocreate table tb01(
a1 int,
a2 varchar(255)
)
go
insert into tb01
select 1,'1,2' union all
select 2,'2,5' union all
select 3,'3,5' union all
select 4,'2,7' union all
select 5,'1,2,8'union all
select 6, '1,5' union all
select 7, '2' union all
select 8, '1,2,5'
goselect * from tb01select * from tb01 where replace(a2,',','') like '12' or replace(a2,',','') like '25' or replace(a2,',','') like '15' or a2 like '2' or replace(a2,',','') like '125'go
drop table tb01
不过我感觉你想查询的结果,不具有规律性,如单个时只是 2, 三个是1,2,5 但1,2,8难处理(1,2,5的排列组合中只要某些组合),所以你查询的结果感觉象是按指定的值进行抽检,
select * from tb01 where replace(a2,',','') = '12' or replace(a2,',','') = '25' or replace(a2,',','') = '15' or a2 = '2' or replace(a2,',','') = '125'like 换成 = 这样可能更好点
select * from a where patindex('%'+a2+',%','1,2,5,')>0
1)','+replace(a2,',',',,')+',' 如(1,5)就变成(,1,,5,);MYSQL用的是concat()连接字符串,貌似+没用
2)replace((,1,,5,),',1,','');
3)replace(replace((,1,,5,),',1,',''),',2,','')
.....以此类推最后结果为空则符合,否则不符合;
在问个问题:where条件中 and 是类似 & 还是 &&?
这个不对吧,你where 后面charindex返回的是位置和0,和前面有什么关系