关于使用in的一条sql语句，走过路过随便进来瞅瞅
table 1字段

id numberic 自增


table2 中有一字段ids，记录table1中多个id，用逗号分割 1,2,3,4

sql：

select * from table1 where id in (select ids from table2)

现在id是数字型，ids为varchar型，这样查询结果肯定不正确，请叫如何正确写这条sql

1.select * from table1 where id in ('1,2,3')---结果不正确
2.select * from table1 where id in (1,2,3)---结果正确

3.select * from table1 where convert(varchar(20),id) in ('1,2,3')---结果不正确
4.select * from table1 where convert(varchar(20),id)in (1,2,3)---结果正确

我将id转换成了varchar再使用in，数据库还是按数字类型来判断吗？何解啊？sqlserver用的不多，求解。

[解决办法]

SQL code

create function [dbo].[split](@SourceSql varchar(8000),@StrSeprate varchar(10))returns @temp table(F1 varchar(100))asbegin    declare @i int    set @SourceSql = rtrim(ltrim(@SourceSql))    set @i = charindex(@StrSeprate,@SourceSql)    while @i >= 1    begin        if len(left(@SourceSql,@i-1))>0        begin            insert @temp values(left(@SourceSql,@i-1))        end        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)        set @i=charindex(@StrSeprate,@SourceSql)    end    if @SourceSql <> ''        insert @temp values(@SourceSql)    returnend select * from split(',,777,,11,,888,88,,1122,888,88,77,,00,,00',',')
[解决办法]
1.select * from table1 where id in ('1,2,3')---结果不正确
2.select * from table1 where id in (1,2,3)---结果正确

对于1可以修改如下：

select * from table1 where ','+'1,2,3'+',' like '%,'+ltrim(id)+',%'

select * from table1 where charindex(','+ltrim(id)+',',','+'1,2,3'+',') > 0
[解决办法]
探讨


引用:
in ('1,2,3')--只是把'1,2,3'当成一个字符串

--如下修改即可
select * from table1 where ltrim（id） in (select ids from table2)


这样修改后结果还是不对啊，去掉''结果才正确

[解决办法]
可以用charindex函数。
select * from table1 where charindex(id,(select ids from table2))>0  
and charindex(id,(select ids from table3))=0
[解决办法]
探讨


把字符串分隔了

[解决办法]


SQL code
--CHARINDEX函数可以实现这个功能select * from table1 where chaxindex(convert(varchar(20),id),(select ids from table2))>0;
[解决办法]
探讨


in ('1,2,3')--只是把'1,2,3'当成一个字符串

--如下修改即可
select * from table1 where ltrim（id） in (select ids from table2)

[解决办法]
e.g.
SQL code
use tempdbgoif object_id('tempdb..#') Is not null    Drop Table #create table #(id int identity)goInsert Into # default valuesgo 200--1Select * from #  Where id in(1,2,3)--2Select * from #  Where patindex('%,'+rtrim(id)+',%',',1,2,3,')>0--3Declare @Input nvarchar(2000),@sql nvarchar(max)Set @Input='1,2,3'Set @sql=N'Select * From # Where id in('+@input+N')'Exec sp_executesql @sql/*id------------------123*/
[解决办法]
用charindex即可。
[解决办法]
SQL code
declare @sql varchar(6000)select @sql='select * from table1 where id in ('+(select ids from table2)+') and id not in ('+(select ids from table3)+');'exec(@sql) 
 
[解决办法]
用FIND_IN_SET可以。
select * from table1 where FIND_IN_SET(id,select ids from table2 where id=1)>0;
[解决办法]
DECLARE @vch_string varchar(max)
DECLARE @chr_delim char(1)
SET @chr_delim = ','
SET @vch_string = '1,2,3'

;WITH nums_cte
AS
(
 SELECT 1 AS n
 UNION ALL
 SELECT n+1 FROM nums_cte
 WHERE n < len(@vch_string)
)
SELECT n - LEN(REPLACE(LEFT(s,n),@chr_delim,'')) + 1 AS pos
      ,SUBSTRING(s,n,CHARINDEX(@chr_delim, s + @chr_delim,n) -n) as ELEMENT
FROM (SELECT @vch_string as s) AS D
JOIN nums_cte
ON n <= LEN(s)
AND SUBSTRING(@chr_delim + s,n,1) = @chr_delim
OPTION (MAXRECURSION 0);
[解决办法]
select * from tb where CHARINDEX ('''' +'aa' + '''',replace(b,'"',''''),0)>0

http://topic.csdn.net/u/20120410/06/c08570c3-e345-48cd-b57c-60e458131adb.html?seed=1800632115&r=78186488#r_78186488
之前我也这样提问题，希望你能从我这里找到一点帮助