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 all_.bh,all_.name,a.sl,b.sl1,c.sl2,c.sl3 from
(
select bh,name from a
union
select bh,name from b
union
select bh,name from c
) all_
left outer join a on all_.bh = a.bh and all_.name = a.name
left outer join b on all_.bh = b.bh and all_.name = b.name
left outer join c on all_.bh = c.bh and all_.name = c.name

Answer 2

当数据记录条数不一致时，使用左联接查询：
select A.bh, A.name , sl, sl1,sl2,sl3
from A
left join B on A.bh= B.bh
left join C on A.bh= C.bh

追问

left join是以左表为主，我上面的三张表中并不一定是A表的数据多，可能A表有两条，B表有3条，结果要把三张表中所有的都显示出来

追答

这样的话是比较麻烦了，我再研究下

Answer 3

select a.bn,a.name,a.s1,b.s2,c.s3
from a,b,c
where a.bh=b.bh and a.bh=c.bh

Answer 4

select * from A
union all
select * from B
union all
select * from C

Answer 5

Try this:

SELECT t.bh, t.name, max(a.sl), max(b.sl1), max(c.sl2), max(c.sl3)
from (SELECT DISTINCT bh, name from a
UNION SELECT DISTINCT bh, name from b
UNION SELECT DISTINCT bh, name from c) t
LEFT JOIN a ON t.bh = a.bh and t.name = a.name
LEFT JOIN b ON t.bh = b.bh and t.name = b.name
LEFT JOIN c ON t.bh = c.bh and t.name = c.name
GROUP BY t.bh, t.name
ORDER BY t.bh