Question

关于oracle多表联合查询的语句如何写？

有三张表如下：
A表
bh name sl
1 a 10
2 b 20
3 c 30
B表
bh name sl1
1 a 5
2 b 20
3 c 25
C表
bh name sl2 sl3
1 a 5 0
2 b 20 0
通过查询想得到如下结果：
bh name sl sl1 sl2 sl3
1 a 10 5 5 0
2 b 20 20 20 0
3 c 30 25
请问oracle中该如何写这个语句？

Answer 1

SELECT nvl(nvl(a.bh,b.bh),c.bh) bh,
nvl(nvl(a.name,b.name),c.name) name,
A.SL,B.SL1,C.SL2,C.SL3
FROM A
full join B on A.bh= B.bh
full join C on A.bh= C.bh

追问

谢谢你！！！

Answer 2

SELECT A.BH,A.NAME,A.SL,B.SL1,C.SL2,C.SL3
FROM A
FULL OUTER JOIN B
ON A.BH=B.BH
AND A.NAME=B.NAME
FULL OUTER JOIN C
ON A.BH=C.BH
AND A.NAME=C.NAME

Answer 3

select a.bn,a.name,a.sl,b.sl1,c.sl2,c.sl3 from a join b on a.bn=b.bn join c on a.bn=c.bn

Answer 4

select a.bh,a.name,a.sl,b.sl1,c.sl2,c.sl3 from a left join b on a.name = b.name left join c on a.name = c.name order by a.name

Answer 5

这个才是真的能用
select A.bh, A.name , sl, sl1,sl2,sl3

from
select distinct bn from (
(select distinct bn from a
union
select distinct bn from b
union
select distinct bn from c))as aa
left join a on a,bh=aa.bh
left join B on A.bh= B.bh
left join C on A.bh= C.bh