表test
ID,A,B
50,hello,world
51,hell,word
表out
ID,C
50,hello
50,world hello
51,hell
51,world
51,lleh查询表test中某个ID对应的A、B均在表out中具有相同ID的C中模糊出现的记录
比如test中,ID=50,A=hello,B=world
out中,ID=50的C有hello和world hello
test的A=hello与C的hello匹配
test的B=world与C的world hello模糊匹配(若C="abc world hello"也是与B=world匹配的)
所以test中第一条记录符合条件,输出
test的ID=51的记录显然不符合条件
求sql语句
ID,A,B
50,hello,world
51,hell,word
表out
ID,C
50,hello
50,world hello
51,hell
51,world
51,lleh查询表test中某个ID对应的A、B均在表out中具有相同ID的C中模糊出现的记录
比如test中,ID=50,A=hello,B=world
out中,ID=50的C有hello和world hello
test的A=hello与C的hello匹配
test的B=world与C的world hello模糊匹配(若C="abc world hello"也是与B=world匹配的)
所以test中第一条记录符合条件,输出
test的ID=51的记录显然不符合条件
求sql语句
create table #ta(ID int,A varchar(10),B varchar(10))
insert into #ta
select 50,'hello','world'
union all select 51,'hell','word'create table #tb(ID int,C varchar(100))
insert into #tb
select 50,'hello'
union all select 50,'world hello'
union all select 51,'hell'
union all select 51,'world'
union all select 51,'lleh'select a.*
from #ta a
inner join #tb b on a.A=b.C
inner join #tb c on a.B=c.C
drop table #ta,#tb
/*
ID A B
------------------------
50 hello world
*/
A=C="hello"则A=C
B=C="world"则B=C
A="hello"
C="others hello others"则A=C
B="world"
C="*world*"则B=C
直接用a.A=b.C,若果ID不相等的两表记录是不是也会被记入,不知道对不对,求解!
51,hell,word匹配:表out
51,hellselect distinct a.*
from #ta a
inner join #tb b on a.id=b.id and (charindex(a.A,b.C)>0 or charindex(a.B,b.C)>0)
/*
ID A B
------------------------
50 hello world
51 hell word*/
test中AB是hello、word,out中id=51的C=hello、world、lleh,AB的hello在C中出现了,但是word没有和world匹配
一, A,B其中之一模湖匹配
select distinct a.*
from #ta a
inner join #tb b on a.id=b.id and (charindex(a.A,b.C)>0 or charindex(a.B,b.C)>0) 二, A,B两者都模湖匹配
select a.*
from #ta a
inner join #tb b on a.id=b.id and (charindex(a.A,b.C)>0 and charindex(a.B,b.C)>0)你看哪个结果是你想要的?
test中AB是hello、word,out中id=51的C=hello、world、lleh,AB的hello在C中出现了,但是word没有和world匹配期望的结果是A=C and B=C
inner join #tb b on a.id=b.id and (charindex(a.A,b.C)>0 or charindex(a.B,b.C)>0)
inner join #tb b on a.id=b.id and (charindex(a.A,b.C)>0 and charindex(a.B,b.C)>0)
test中AB是hello、word,out中id=51的C=hello、world、lleh,AB的hello在C中出现了,但是word没有和world匹配那就取上面的方法二,--------->A,B两者都模湖匹配
test中AB是hello、word,out中id=51的C=hello、world、lleh,AB的hello在C中出现了,但是word没有和world匹配那就取上面的方法二,--------->A,B两者都模湖匹配
遇到个问题:create table #ta(ID int,A varchar(10),B varchar(10))
insert into #ta
select 50,'hello','world'
union all select 51,'hell','word'
create table #tb(ID int,C varchar(100))
insert into #tb
select 50,'hello'
union all select 50,'world'
union all select 51,'hell'
union all select 51,'world'
union all select 51,'lleh'
select distinct a.*
from #ta a
inner join #tb b on a.ID=b.ID
and (CHARINDEX(a.A,b.C)>0 and CHARINDEX(a.B,b.C)>0)
drop table #ta,#tbunion all select 50,'world hello'改成
union all select 50,'world'
就没有输出了,ID=50是匹配的
test中ID=50,A=hello,B=world分别与out中ID=50的两条记录中的C(hello,world)匹配
原先out表中记录50,'world hello'的C='world hello'与A=hello和B=world同时匹配了,但是期望的不是同一条记录中AB与C同时匹配,而是AB与具有相同ID的所有C匹配