第1表有key:a,b。非空:c,d,e
第2表有key:c。非空:f
第3表有key:d,e,f。非空:g现在根据已知的a,以第一表为主表,得出b,c,g字段。请问如何解决
主要麻烦是
写2.f=3.f(+)肯定不行
若写2.f=3.f会丢数据
第2表有key:c。非空:f
第3表有key:d,e,f。非空:g现在根据已知的a,以第一表为主表,得出b,c,g字段。请问如何解决
主要麻烦是
写2.f=3.f(+)肯定不行
若写2.f=3.f会丢数据
from 第1表 a1 left join 第2表 a2 on a1.c=a2.c
left join 第3表 a3 on a1.d=a3.d and a1.e=a3.e and a2.f=a3.f
第2表有key:c。非空:f
第3表有key:d,e,f。非空:gselect m1.b, m1.c , m4.g from 第1表 as m1 , (select m2.c , m3.d , m3.e , m3.f , m3.g from 第2表 as m2 , 第3表 as m3 where m2.f = m3.f) as m4
where m1.c = m4.c
我刚试过,检索行有重复。
and b1.d=b3.d and b2.f=b3.f and b1.a=''b1,b2,b3分别代表三个表
create table tb1
(
a varchar not null ,
b varchar not null ,
c varchar ,
d varchar ,
e varchar ,
)
create table tb2
(
c varchar not null ,
f varchar ,
)create table tb3
(
d varchar not null ,
e varchar not null ,
f varchar not null ,
g varchar ,
)--数据
insert into tb1
select '1','2','1','3',null union all
select '2','4','4','d','1' union all
select '3','d','1','s',null union all
select '4','2','3','2','d' union all
select '5','3','1','3',null insert into tb2
select '1','2' union all
select '2','1' union all
select '3',null insert into tb3
select '1','2','3',null union all
select '2','4','d','1' union all
select '3','d','1','s' union all
select '4','2','3','2' union all
select '5','3','1',null
select x.a,x.b,x.c,tb3.g
from
(
select tb1.a ,tb1.b,tb1.d,tb1.c,tb1.e,tb2.f
from tb1 left join tb2 on tb1.c=tb2.c
) x
left join tb3 on x.d=tb3.d and x.e=tb3.e and x.f=tb3.f
---- ---- ---- ----
1 2 1 NULL
2 4 4 NULL
3 d 1 NULL
4 2 3 NULL
5 3 1 NULL(所影响的行数为 5 行)
select 1.b,1.c,tt.g
from 1,3,(select * from 1,2 where 1.c=2.c(+)) tt
where
1.a='x'
and tt.a=1.a(+)
and 1.d=3.d(+)
and 1.e=3.e(+)
select 1.b,1.c,tt.g
from 1,3,(select * from 1,2 where 1.c=2.c(+)) tt
where
1.a='x'
and 1.a=tt.a
and tt.d=3.d(+)
and tt.e=3.e(+)
and tt.f=3.f(+)
多几次left join
where m1.c = m4.c