sql如何实现模糊子查询？

Question

我现在有A,B表：A：ID  name                                 55   一                                 56   二                                 57   一                            B：ID     aid                                 1     55|56                                 2     6058|59                                 3     58|57现在我想查询B表里面有A表“name”为“一”的数据。结果应该是：B表的  1 和3条。请问这个sql怎么写？下面这个我写的：有错误。求正解！ select ID from B where aid like  (select '%'+convert(varchar(20),ID)+'%' from A where name='一')。

Answer 1

/*写测试数据
create table #A(id int ,name varchar(10))
insert into #A values(55,'一')
insert into #A values(56,'二')
insert into #A values(57,'一')

create table #B(id int ,aid varchar(20))
insert into #b values(1,'55|56')
insert into #b values(2,'6058|59')
insert into #b values(3,'58|57')

*/
select b.id from #b b ,#a a where a.name ='一' and b.aid like '%'+ltrim(rtrim(convert(varchar(20),a.id)))+'%'
实际语法是
select b.id from b ,a where a.name ='一' and b.aid like '%'+ltrim(rtrim(convert(varchar(20),a.id)))+'%'

测试结果：
id
1
3

Answer 2

select B.ID from B inner join A on B.aid = A.ID where A.name = '一'

是这个意思么

Answer 3

select id from b where substring(aid,charindex('1',aid)+1,len(aid)) in （select id from a where name=‘一’）