sqlserver select a from b where a in (X)

Question

begin
declare @3 varchar(2000)
set @3 = '2012019339,2013000613,2013000128,2012019883,2012019018,2013000504,2013000683,2013000684,2013000435,2013001349,2013001299,2013001387,2013001103,2013001556,2013001518,2013000978,2013000268,2013000594,2013001864,2013001147,2013000699,2013000233,2013001206,2013001207,2013001044,2013001297,2013001298,2013001582,2013001689,2013001578,2013001579,2013001687,2013001947,2013002011,2013001837,2013001862,2013001835,2013001989,2013001992,2013001231,2013001277,2013001633,2013001108,2013001090,2013000998,2013001171,2013001247,2013001258,2013000916,2013001030,2013001158,2013001354,2013001604,2013001674,2013001552,2013001928,2013001693,2013001705,2013001749,2013002072,2013001694,2013001748,2013002043,2013002044,2013001990,2013000959,2013001251,2013001743,2013001957,2013001742,2013001550,2013000778,2013001594,2013001348,2013001069,2013001070,2013001759,2013001760,2013001916,2013001969,2013001963,2013002166,2013001858,2013001965,2013001967,2013002061,2013001146,2013001145,2013001961,2013001964,2013001350,2013001356,2013000515,2013000709,2013000973,2013000349,2013001977'
select * from aa where bb in (
select RTrim( ''''+REPLACE ( SUBSTRING (@3 , 1 , LEN(@3)), ',' ,''',''')+'''' ))
end
出不来数据
begin
select * from aa where bb in ( '2012019339','2013000613','2013000128','2012019883','2012019018','2013000504','2013000683','2013000684','2013000435','2013001349','2013001299','2013001387','2013001103','2013001556','2013001518','2013000978','2013000268','2013000594','2013001864','2013001147','2013000699','2013000233','2013001206','2013001207','2013001044','2013001297','2013001298','2013001582','2013001689','2013001578','2013001579','2013001687','2013001947','2013002011','2013001837','2013001862','2013001835','2013001989','2013001992','2013001231','2013001277','2013001633','2013001108','2013001090','2013000998','2013001171','2013001247','2013001258','2013000916','2013001030','2013001158','2013001354','2013001604','2013001674','2013001552','2013001928','2013001693','2013001705','2013001749','2013002072','2013001694','2013001748','2013002043','2013002044','2013001990','2013000959','2013001251','2013001743','2013001957','2013001742','2013001550','2013000778','2013001594','2013001348','2013001069','2013001070','2013001759','2013001760','2013001916','2013001969','2013001963','2013002166','2013001858','2013001965','2013001967','2013002061','2013001146','2013001145','2013001961','2013001964','2013001350','2013001356','2013000515','2013000709','2013000973','2013000349','2013001977')

end
出的来数据

求解？？？

Answer 1

1、先说先面的为什么不行：
前面的IN函数的参数是一个结果集，而的结果集是一个经过修改的长字串，不是多个记录的集合。

2、再说后面的：
BB字段应该是类似'2012019339'这样的短数据。所以不可有一个与你得到的长字串一样。
而后面的是一个以逗号分隔的字串集合，只要BB是其中一个字串，就符合查询条件。

3、解决方法：
你应该是想用这样的方式代替一个长长的查询语句：
select * from aa where bb in (@3)
用后面的几行语句替换你的这一行：
select * from aa where bb in (
select RTrim( ''''+REPLACE ( SUBSTRING (@3 , 1 , LEN(@3)), ',' ,''',''')+'''' ))
即可：
declare @sqlstr Nvarchar(1000)
set @3= RTrim( ''''+REPLACE ( SUBSTRING (@3 , 1 , LEN(@3)), ',' ,''',''')+'''' )

set @sqlstr=N'select * from aa where bb in('+@3+')'

exec sp_executesql @sqlstr追问

谢谢，一开始我也这样做，但是由于类似‘2012001234’..............太多，后来查询的时候有反应，但是结果还是和没有加这个条件一样，我就在怀疑是不是sqlserver对in（x）条件有限制，想想也不对，都糊涂了，最后把长字符串拆成我要的一个个短的放表里才搞定，很简单的事，但是很闹心，谢谢，多多交流，我是新手

Answer 2

select * from aa where charindex(','+convert(varchar,b)+',',','+@3+',')>0