有多张表,如下所示,每张表有两个字段Name和Countx
表A
Name Count1
aa 1
bb 2
表B
Name Count2
aa 2
cc 3
表c
Name Count3
aa 4
cc 5
现在想把这几张表中的数据统计到一张表中
表ABC
Name Count1 Count2 Count3
aa 1 2 4
bb 2 0 0
cc 0 3 5如果是两张表可以用full outer join,现在是多张表,该怎么办呢?outer join支持多张表的操作吗?还是有别的什么方法?
表A
Name Count1
aa 1
bb 2
表B
Name Count2
aa 2
cc 3
表c
Name Count3
aa 4
cc 5
现在想把这几张表中的数据统计到一张表中
表ABC
Name Count1 Count2 Count3
aa 1 2 4
bb 2 0 0
cc 0 3 5如果是两张表可以用full outer join,现在是多张表,该怎么办呢?outer join支持多张表的操作吗?还是有别的什么方法?
max(decode(c,'c1',Count1,0)),
max(decode(c,'c2',Count1,0)),
max(decode(c,'c3',Count1,0))
from
(
select a.*,'c1' c from a
union all
select b.*,'c2' c from b
union all
select c.*,'c3' c from c
)t
group by Name
from A full outer join B full outer join C
where a.Name = b.Name and b.Name = c.Name
from A,B,C
where a.name=b.name(+) and a.name=c.name(+)
from a full outer join b on a.Name=b.Name
full outer join c on b.Name=c.Name
order by name
from
(SELECT NAME, COUNT1,0 COUNT2,0 COUNT3 FROM A
UNION ALL
SELECT NAME, 0 COUNT1,COUNT2,0 COUNT3 FROM B
UNION ALL
SELECT NAME, 0 COUNT1,0 COUNT2,COUNT3 FROM C) TABLEA
GROUP BY NAME