select a.*,b.* from BTb as a inner join ATb as b on charindex(','+str(b.AID)+',',',3,5,7,')>0--全文索引有非常强的查询功能。可以利用使用带有 <weighted_term> 的 CONTAINS 来操作。 --呵呵,全文索引也很麻烦。全文填充是件头疼的事情。
select * from atb a,btb b where charindex(','+convert(varchar(10),a.aid)+',',','+b.baid+',')>0 and bid=5
declare @ATb table(AID int ,AName varchar(50), Atelephone varchar(50)) insert @ATb select 1,'Mr.Liu1','0571-8888881' union all select 2,'Mr.Liu2','0571-8888881' union all select 3,'Mr.Liu3','0571-8888881' union all select 4,'Mr.Liu4','0571-8888881' union all select 5,'Mr.Liu5','0571-8888881' union all select 6,'Mr.Liu6','0571-8888881' union all select 7,'Mr.Liu7','0571-8888881' declare @BTb table(BID int,BAID varchar(100),BAMail varchar(100))insert @BTb select 4,'5,7','[email protected]' union all select 5,'3,5,7','[email protected]'select b.*,a.* from @BTb b, @ATb a where b.BID=5 and charindex(','+cast(a.AID as varchar)+',',','+B.BAID+',')>0结果:5 3,5,7 [email protected] 3 Mr.Liu3 0571-8888881 5 3,5,7 [email protected] 5 Mr.Liu5 0571-8888881 5 3,5,7 [email protected] 7 Mr.Liu7 0571-8888881
--呵呵,全文索引也很麻烦。全文填充是件头疼的事情。
where charindex(','+convert(varchar(10),a.aid)+',',','+b.baid+',')>0 and bid=5
insert @ATb
select 1,'Mr.Liu1','0571-8888881' union all
select 2,'Mr.Liu2','0571-8888881' union all
select 3,'Mr.Liu3','0571-8888881' union all
select 4,'Mr.Liu4','0571-8888881' union all
select 5,'Mr.Liu5','0571-8888881' union all
select 6,'Mr.Liu6','0571-8888881' union all
select 7,'Mr.Liu7','0571-8888881' declare @BTb table(BID int,BAID varchar(100),BAMail varchar(100))insert @BTb
select 4,'5,7','[email protected]' union all
select 5,'3,5,7','[email protected]'select b.*,a.*
from @BTb b, @ATb a
where b.BID=5 and charindex(','+cast(a.AID as varchar)+',',','+B.BAID+',')>0结果:5 3,5,7 [email protected] 3 Mr.Liu3 0571-8888881
5 3,5,7 [email protected] 5 Mr.Liu5 0571-8888881
5 3,5,7 [email protected] 7 Mr.Liu7 0571-8888881