select a.*,b.* from tb1 a join (select d,e from tb2 union select d,f from tb2) b on a.a=b.e
select * from tb2 a join tb1 b on a.e = b.a join tb1 c on a.f = c.a
select a.*,b.* from t1 a inner join t2 b on a.a=b.e or a.a=b.f
create table t1(a int,b int,c int) insert into t1 select 1,234,534 insert into t1 select 2,324,439 insert into t1 select 3,94,9438 insert into t1 select 4,843,92 insert into t1 select 5,985,894 create table t2(d varchar(10),e int,f int) insert into t2 select 'aaa',2,19 insert into t2 select 'bbb',5,33 insert into t2 select 'ccc',10,4 go select a.*,b.* from t1 a inner join t2 b on a.a=b.e or a.a=b.f /* a b c d e f ----------- ----------- ----------- ---------- ----------- ----------- 2 324 439 aaa 2 19 5 985 894 bbb 5 33 4 843 92 ccc 10 4(3 行受影响)*/ go drop table t1,t2
我想要把t1表里的符合 a.a=b.e or a.a=b.f 的2条数据都显示出来,不是 a.a=b.e or a.a=b.f 中任意一个符合就可以
如果是"与"关系的话,那必定要b.e=b.f,貌似与楼主说的不太相同.
if object_id('A','U') is not null drop table A go create table A ( a varchar(10), b varchar(10), c varchar(10) ) go insert into A select 'a','b','c' union all select 'a1','b1','c1' go if object_id('B','U') is not null drop table B go create table B ( d varchar(10), e varchar(10), f varchar(10) ) go insert into B select 'd','e','f' union all select 'd1','a','c' union all select 'd2','a','a' go select t1.*,t2.d from a t1 inner join b t2 on t1.a=t2.e and t1.a=t2.f /* a b c d ---------- ---------- ---------- ---------- a b c d2(1 行受影响)*/是这样吧?
create table tb1 ( a varchar(10), b varchar(10), c varchar(10) ) insert into tb1 select 'a','b','c' union all select 'b','b1','c1' union all select 'c','ab','art' gocreate table tb2 ( d varchar(10), e varchar(10), f varchar(10) ) insert into tb2 select 'd','a','c' union all select 'd1','b','c' union all select 'd2','c','a' goselect a.d,b.a,c.a as ca from tb2 a join tb1 b on a.e = b.a join tb1 c on a.f = c.adrop table tb1,tb2/***************d a ca ---------- ---------- ---------- d a c d1 b c d2 c a(3 行受影响)
create table tb1 ( a varchar(10), b varchar(10), c varchar(10) ) insert into tb1 select 'a','b','c' union all select 'b','b1','c1' union all select 'c','ab','art' gocreate table tb2 ( d varchar(10), e varchar(10), f varchar(10) ) insert into tb2 select 'd','a','c' union all select 'd1','b','c' union all select 'd2','c','a' goselect a.d,b.*,c.* from tb2 a join tb1 b on a.e = b.a join tb1 c on a.f = c.adrop table tb1,tb2/********************d a b c a b c ---------- ---------- ---------- ---------- ---------- ---------- ---------- d a b c c ab art d1 b b1 c1 c ab art d2 c ab art a b c(3 行受影响)
from tb1 a
join
(select d,e from tb2 union select d,f from tb2) b
on a.a=b.e
from tb2 a join tb1 b on a.e = b.a
join tb1 c on a.f = c.a
insert into t1 select 1,234,534
insert into t1 select 2,324,439
insert into t1 select 3,94,9438
insert into t1 select 4,843,92
insert into t1 select 5,985,894
create table t2(d varchar(10),e int,f int)
insert into t2 select 'aaa',2,19
insert into t2 select 'bbb',5,33
insert into t2 select 'ccc',10,4
go
select a.*,b.* from t1 a inner join t2 b on a.a=b.e or a.a=b.f
/*
a b c d e f
----------- ----------- ----------- ---------- ----------- -----------
2 324 439 aaa 2 19
5 985 894 bbb 5 33
4 843 92 ccc 10 4(3 行受影响)*/
go
drop table t1,t2
我想要把t1表里的符合 a.a=b.e or a.a=b.f 的2条数据都显示出来,不是 a.a=b.e or a.a=b.f 中任意一个符合就可以
if object_id('A','U') is not null
drop table A
go
create table A
(
a varchar(10),
b varchar(10),
c varchar(10)
)
go
insert into A
select 'a','b','c' union all
select 'a1','b1','c1'
go
if object_id('B','U') is not null
drop table B
go
create table B
(
d varchar(10),
e varchar(10),
f varchar(10)
)
go
insert into B
select 'd','e','f' union all
select 'd1','a','c' union all
select 'd2','a','a'
go
select t1.*,t2.d from a t1 inner join b t2 on t1.a=t2.e and t1.a=t2.f
/*
a b c d
---------- ---------- ---------- ----------
a b c d2(1 行受影响)*/是这样吧?
create table tb1
(
a varchar(10),
b varchar(10),
c varchar(10)
)
insert into tb1
select 'a','b','c' union all
select 'b','b1','c1' union all
select 'c','ab','art'
gocreate table tb2
(
d varchar(10),
e varchar(10),
f varchar(10)
)
insert into tb2
select 'd','a','c' union all
select 'd1','b','c' union all
select 'd2','c','a'
goselect a.d,b.a,c.a as ca
from tb2 a join tb1 b on a.e = b.a
join tb1 c on a.f = c.adrop table tb1,tb2/***************d a ca
---------- ---------- ----------
d a c
d1 b c
d2 c a(3 行受影响)
create table tb1
(
a varchar(10),
b varchar(10),
c varchar(10)
)
insert into tb1
select 'a','b','c' union all
select 'b','b1','c1' union all
select 'c','ab','art'
gocreate table tb2
(
d varchar(10),
e varchar(10),
f varchar(10)
)
insert into tb2
select 'd','a','c' union all
select 'd1','b','c' union all
select 'd2','c','a'
goselect a.d,b.*,c.*
from tb2 a join tb1 b on a.e = b.a
join tb1 c on a.f = c.adrop table tb1,tb2/********************d a b c a b c
---------- ---------- ---------- ---------- ---------- ---------- ----------
d a b c c ab art
d1 b b1 c1 c ab art
d2 c ab art a b c(3 行受影响)