在使用IN的时候遇到类型转换问题，帮忙解决。

[forcall]

例如我把a表的a_id关键字存储到b表的一个字段里，例如

a表
a_id
1
2
3
4
5
6
7
8

b表
b_id a_id
1 1,2,3,6,7,8


我在使用SQL语句的时候

select * from a where a_id in (select a_id from b where b_id=1)的时候，系统提示

“在将 nvarchar 值 '1,2,3,6,7,8' 转换成数据类型 int 时失败”

该怎么解决啊？

[andy_liucj]

学习了

[andy_liucj]

if object_id('ta') is not null drop table ta
create table ta(a_id int)
insert into  ta values(1)
insert into  ta values(2)
insert into  ta values(3)
insert into  ta values(24)
insert into  ta values(35)
insert into  ta values(46)
insert into  ta values(7)
insert into  ta values(100)
--select * from a

if object_id('tb') is not null drop table tb     
create table tb (b_id int,a_id varchar(100))
insert into tb (b_id,a_id) values(1,'1,2,3,6,7,8')

declare @sql varchar(1000)
select @sql=a_id from tb where b_id=1
exec('select * from ta where a_id in ('+@sql+')')

动态执行

[utpcb]

select ta.* from a ta ,b tb where ta.a_id=tb.a_id
and tb.b_id=1
兄弟你这样写不快点么 而且还对

[jwdream2008]

select * from a where ltrim(a_id) in (select a_id from b where b_id=1)

[华夏小卒]

select * from a where ','+ltrim(a_id)+',' in (select ','+a_id+',' from b where b_id=1)

[水族杰纶]

select t.*
from a t ,b s
where charindex(','+ltrim(t.a_id)+',',','+s.a_id +',')>0

[百年树人]

select a.*
from a,b
where charindex(','+ltrim(aid)+',',','+b.a_id+',')>0
and b.b_id=1

[forcall]

[Quote=引用 1 楼 jwdream2008 的回复:]
SQL code
select * from a where ltrim(a_id) in (select a_id from b where b_id=1)
[/Quote]

速度好快啊。试试

[水族杰纶]

select t.* 
from a t ,b s
where charindex(','+ltrim(t.a_id)+',',','+s.a_id +',')>0

[水族杰纶]

[Quote=引用 6 楼 wufeng4552 的回复:]
SQL code
select t.*
from a t ,b s
where charindex(','+ltrim(t.a_id)+',',','+s.a_id +',')>0
[/Quote]
忘记了个条件
参考4#树哥的

[jwdream2008]

[Quote=引用 4 楼 josy 的回复:]
SQL code
select a.*
from a,b
where charindex(','+ltrim(aid)+',',','+b.a_id+',')>0
and b.b_id=1
[/Quote]

[百年树人]

[Quote=引用 5 楼 forcall 的回复:]
引用 1 楼 jwdream2008 的回复:
SQL code
select * from a where ltrim(a_id) in (select a_id from b where b_id=1)
速度好快啊。试试
[/Quote]
修正笔误

select a.*
from a,b
where charindex(','+ltrim(a.a_id)+',',','+b.a_id+',')>0
and b.b_id=1

[forcall]

josy和wufeng4552都正确，能简单说一下原理吗？

[幸运的意外]

select a.*
from a,b
where charindex(','+ltrim(a.a_id)+',',','+b.a_id+',')>0
and b.b_id=1

主要是，b表里的a_id已经不再是int类型了，是个字符串，所以在LZ的语句里出现了类型不一致的的错误。
树人大哥的代码就是检测，b表里的a_id里面有没有包含从a表里查出来的值。

[hokor]

DECLARE @A table (a_id int)
insert into  @A values(1)
insert into  @A values(2)
insert into  @A values(13)
insert into  @A values(24)
insert into  @A values(35)
insert into  @A values(46)
insert into  @A values(78)
insert into  @A values(100)
select * from @A
     

--1,2,3,4,5,6,7,8
DECLARE @B table (b_id int,a_id varchar(100))
insert into @B (b_id,a_id) values(1,'1,13,35,46,7,78')
select * from  @b

select * from @A where charindex(ltrim(a_id)+',',(select a_id from @b where b_id = 1)) <> 0

[永生天地]

[Quote=引用 10 楼 forcall 的回复:]
josy和wufeng4552都正确，能简单说一下原理吗？
[/Quote]
charindex函数