获取到的数据格式是varchar类型如('1001','分类1','分类2')
create table demo(id int,[name] varchar(20),code varchar(20))insert into demo
select 3,'aa','1001' union all
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001'select * from demoid name code
----------- -------------------- --------------------
3 aa 1001
4 bb 2001
5 cc 3001
6 分类1 4001
7 分类2 5001
通过上面的逗号分割字符串,去获取数据,得到的字符串在demo表中对于的name和code
结果应该是想匹配的3条数据
create table demo(id int,[name] varchar(20),code varchar(20))insert into demo
select 3,'aa','1001' union all
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001'select * from demoid name code
----------- -------------------- --------------------
3 aa 1001
4 bb 2001
5 cc 3001
6 分类1 4001
7 分类2 5001
通过上面的逗号分割字符串,去获取数据,得到的字符串在demo表中对于的name和code
结果应该是想匹配的3条数据
or
code in('1001','分类1','分类2')
http://blog.csdn.net/peng13nannan/article/details/7418927
where name in('1001','分类1','分类2') or code in('1001','分类1','分类2')
DECLARE @Str VARCHAR(1000)SET @Str = '1001,分类1,分类2'
create table demo(id int,[name] varchar(20),code varchar(20))insert into demo
select 3,'aa','1001' union all
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001'select * from demo
WHERE CHARINDEX(',' + name + ',',',' + @Str + ',') > 0 OR CHARINDEX(',' + code + ',',',' + @Str + ',') > 0
/*
id name code
3 aa 1001
6 分类1 4001
7 分类2 5001
*/
select * from demo
where ','+name+',' like ','+'1001,分类1,分类2'+','
or ','+code+',' like ','+'1001,分类1,分类2'+','
declare @s varchar(20)
set @s='1001,分类1,分类2'
select * from demo
where @s like '%'+name+'%' or @s like '%'+code+'%'
你用条件去匹配字段 直接反掉了
declare @s varchar(20)
set @s='1001,分类1,分类2'
select * from demo
where name like '%'+@s+'%' or code like '%'+@s+'%'
如这样写的话 查询出来的也是空数据
select 3,'aa','1001' union all
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001'declare @s varchar(20)
set @s='1001,分类1,分类2'
select * from demo
where @s like '%'+name+'%' or @s like '%'+code+'%'/**
id name code
----------- -------------------- --------------------
3 aa 1001
6 分类1 4001
7 分类2 5001(3 行受影响)
**/
你的查询条件就是逗号隔开的数据吗?
select 3,'aa','1001' union all
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001' union all
select 8,'分类','2002'
godeclare @s varchar(20)
set @s='1001,分类1,分类2'select * from demo where
','+@s+',' like '%,'+name+',%'
or
','+@s+',' like '%,'+code+',%'
/*id name code
----------- -------------------- --------------------
3 aa 1001
6 分类1 4001
7 分类2 5001(所影响的行数为 3 行)应该要加上逗号。防止出问题