如有A表和B表
A表
name,count,type
Tom, 20, 1
Jim, 10, 1
Jony, 15, 1B表
name,count,type
Helen, 20, 1
Jim, 10, 1
Jony, 15, 1要的查询结果是:
name, counta, countb,count
Tom, 20, 0, 20
Jim, 10, 10, 20
Jony, 15, 15, 30
Helen, 0, 20, 20Oracle数据整合
A表
name,count,type
Tom, 20, 1
Jim, 10, 1
Jony, 15, 1B表
name,count,type
Helen, 20, 1
Jim, 10, 1
Jony, 15, 1要的查询结果是:
name, counta, countb,count
Tom, 20, 0, 20
Jim, 10, 10, 20
Jony, 15, 15, 30
Helen, 0, 20, 20Oracle数据整合
max(nvl(a.count, 0)) counta,
max(nvl(b.count, 0)) countb,
sum(nvl(a.count, 0) + nvl(b.count, 0)) count
from a
full join b
on a.name = b.name
group by nvl(a.name, b.name);
先查出总用户,然后根据每个用户写sql统计
select y.*,(y.counta+y.countb)count from
(select x.name,(select sum(a.count) from a where a.name=x.name) counta,
(select sum(b.count) from b where b.name=x.name) countb
(select name from a union select name from b)x)y
没实际执行过,但思路不会有问题