如何实现这个的sql语句

[aboys]

select distinct col1 FROM tableName where
col2 in (case when 1=1 then 'bbb','aaa' else '' end)
提示错误:
服务器: 消息 170，级别 15，状态 1，行 2
第 2 行: ',' 附近有语法错误。

改为:
select distinct col1 FROM tableName where
col2 in (case when 1=1 then '''bbb'',''aaa''' else '' end)
没有错误,但是找不到记录,(col2中有这样的记录)

[CrazyFor]

TRY:


select distinct col1 FROM tableName where
(1=1 and charindex(','''+col2+''',' , ','+ '''bbb'',''aaa''' +','))

逻辑和你上面的语句是一样的.

[CrazyFor]

TRY:


select distinct col1 FROM tableName where
(1=1 and charindex(','''+col2+''',' , ','+ '''bbb'',''aaa''' +','))

逻辑和你上面的语句是一样的.

[aboys]

谢谢CrazyFor
不过我要的是这个的格式:

declare @a int
set @a=1
select distinct col1 FROM tableName where
col2 in (case when @a=1 then 'bbb','aaa' else '' end)

大家想想办法吧

[zjcxc]

declare @a int
set @a=1
select distinct col1 FROM tableName where
(@a=1 and col2 in ('bbb','aaa')) or (@a<>1 and col2 in(''))

[aboys]

to zjcxc:

sqlStr = "select distinct ITEMNMBR FROM IV00101 where USCATVLS_1 in (case when '" & strbrand & "'='' then '" & txtbrand_txt &"' else '" & strbrand & "' end) "

是这个意思的,当变量strbrand不为空时,查找所有USCATVLS_1 in 变量strbrand的记录
当strbrand为空时,查找所有USCATVLS_1 in 变量txtbrand_txt的记录
其中txtbrand_txt的格式是:'aaa','bb','cc'......
strbrand的格式是 'aaa'

请问如何解决呢?................急等

[CrazyFor]

一样逻辑的语句:

select distinct col1 FROM tableName where
(@a=1 and charindex(','''+col2+''',' , ','+ '''bbb'',''aaa''' +','))
or
(@a<>1 and col2='')

[pengdali]

select distinct col1 FROM tableName where case when 1=1 then ',bbb,aaa,' else '' end like '%,'+col2+',%'

[zjcxc]

那应该这样写:

sqlStr = "select distinct ITEMNMBR FROM IV00101 where USCATVLS_1 in ('" & iif(strbrand="",txtbrand_txt,strbrand) & "')"