使用full joinselect nvl(a.projectid, b.projectid), a.downloadIncome, b.adIncome, case when a.downloadIncome is null then b.time else a.time end from a full join b on a.projectid = b.projectid;
select projectid,sum(downloadIncome),sum(adIncome),time from ( select projectid,downloadIncome ,0 as adIncome , time from A表 union all select projectid ,0, adIncome ,time from B表 ) t group by projectid,time order by time
--还是写一个吧,2楼少关联了一个条件 with a as( select 1 projectid, 100 downloadincome, date'2010-04-10' time from dual union all select 1 projectid, 200 downloadincome, date'2010-04-11' time from dual), b as( select 1 projectid, 100 adincome, date'2010-04-10' time from dual union all select 1 projectid, 300 adincome, date'2010-04-12' time from dual)
SELECT nvl(a.projectid, b.projectid) projectid, nvl(a.downloadincome, 0) downloadincome, nvl(b.adincome, 0) adincome, nvl(a.time, b.time) TIME FROM a FULL OUTER JOIN b ON (a.projectid = b.projectid AND a.time = b.time);
SQL> with a as (select 1 projectid,100 downloadIncome, to_date('2010-04-10','yyyy-mm-dd') time from dual 2 union 3 select 1 projectid,200 downloadIncome, to_date('2010-04-11','yyyy-mm-dd') time from dual 4 ), 5 b as (select 1 projectid,100 adIncome, to_date('2010-04-10','yyyy-mm-dd') time from dual 6 union 7 select 1 projectid,300 adIncome, to_date('2010-04-12','yyyy-mm-dd') from dual 8 ) 9 select a.downloadincome,b.adincome,nvl(to_char(a.time,'yyyy-mm-dd'),to_char(b.time,'yyyy-mm-dd')) time 10 from a full outer join b on (a.projectid=b.projectid and a.downloadincome=b.adincome) 11 order by 1 12 /
a.downloadIncome,
b.adIncome,
case when a.downloadIncome is null then b.time
else a.time
end
from a full join b on a.projectid = b.projectid;
from (
select projectid,downloadIncome ,0 as adIncome , time from A表
union all
select projectid ,0, adIncome ,time from B表
) t
group by projectid,time
order by time
with a as(
select 1 projectid, 100 downloadincome, date'2010-04-10' time from dual union all
select 1 projectid, 200 downloadincome, date'2010-04-11' time from dual),
b as(
select 1 projectid, 100 adincome, date'2010-04-10' time from dual union all
select 1 projectid, 300 adincome, date'2010-04-12' time from dual)
SELECT nvl(a.projectid, b.projectid) projectid,
nvl(a.downloadincome, 0) downloadincome,
nvl(b.adincome, 0) adincome,
nvl(a.time, b.time) TIME
FROM a
FULL OUTER JOIN b
ON (a.projectid = b.projectid AND a.time = b.time);
2 union
3 select 1 projectid,200 downloadIncome, to_date('2010-04-11','yyyy-mm-dd') time from dual
4 ),
5 b as (select 1 projectid,100 adIncome, to_date('2010-04-10','yyyy-mm-dd') time from dual
6 union
7 select 1 projectid,300 adIncome, to_date('2010-04-12','yyyy-mm-dd') from dual
8 )
9 select a.downloadincome,b.adincome,nvl(to_char(a.time,'yyyy-mm-dd'),to_char(b.time,'yyyy-mm-dd')) time
10 from a full outer join b on (a.projectid=b.projectid and a.downloadincome=b.adincome)
11 order by 1
12 /
DOWNLOADINCOME ADINCOME TIME
-------------- ---------- ----------
100 100 2010-04-10
200 2010-04-11
300 2010-04-12
SQL>
谢谢~!