with t1 as ( select date'2001-1-1' as fdate, 'com1' as com, 100 as val from dual union all select date'2004-2-4', 'com2', 300 from dual union all select date'2004-5-4', 'com1', 300 from dual ), t2 as ( select date'1995-1-1' as fdate, 'com1' as com, 300 as val from dual union all select date'2002-2-21', 'com2', 300 from dual union all select date'2012-5-4', 'com2', 300 from dual ), t3 as ( select date'1997-1-1' as fdate, 'com2' as com, 400 as val from dual union all select date'1999-9-9', 'com1', 200 from dual union all select date'2012-5-20', 'com2', 300 from dual ) select fdate,com,sum(val1),sum(val2),sum(val3) from ( select fdate,com,val as val1,0 as val2,0 as val3 from t1 union select fdate,com,0,val,0 from t2 union select fdate,com,0,0,val from t3 ) group by fdate,comFDATE COM SUM(VAL1) SUM(VAL2) SUM(VAL3) ------------------------- ---- ---------------------- ---------------------- ---------------------- 2012-05-04 00:00:00 com2 0 300 0 2012-05-20 00:00:00 com2 0 0 300 1997-01-01 00:00:00 com2 0 0 400 1999-09-09 00:00:00 com1 0 0 200 2004-02-04 00:00:00 com2 300 0 0 2001-01-01 00:00:00 com1 100 0 0 2002-02-21 00:00:00 com2 0 300 0 2004-05-04 00:00:00 com1 300 0 0 1995-01-01 00:00:00 com1 0 300 0
最多外层再加个 nvl ,就完全符合楼主的要求了。
select t3.fdate||t1.fdate||t2.fdate,t1.val,t2.val,t3.val from t1 full join t2 on t1.fdate=t2.fdate full join t3 on t1.fdate=t3.fdate
select * from ( select case when t1.val||t2.val is null then t3.fdate when t1.val||t3.val is null then t2.fdate when t2.val||t3.val is null then t1.fdate end fdate, t1.com||t2.com||t3.com com,t1.val v1,t2.val v2,t3.val v3 from t1 full join t2 on t1.fdate=t2.fdate full join t3 on t1.fdate=t3.fdate ) order by fdate
select time,company,value1,null as value2,null as value3 from table1 union all select time,company,null as value1,value2,null as value3 from table2 union all select time,company,null as value1,null as value2,value3 from table3
select time,company,sum(value1),sum(value2),sum(value3) from (select time,company,valsue as value1,0 as valsue2,0 as value3 from table1 union all select time,company,0 as valsue1,valsue,0 as value3 from table1 union all select time,company,0 as valsue1,0 as valsue2,value from table1 ) group by time,company order by time如果 两个或两以上的表中 在同一个时间,同一个公司都有数据,所以用了group by time,company
t1 as (
select date'2001-1-1' as fdate, 'com1' as com, 100 as val from dual
union all
select date'2004-2-4', 'com2', 300 from dual
union all
select date'2004-5-4', 'com1', 300 from dual
),
t2 as (
select date'1995-1-1' as fdate, 'com1' as com, 300 as val from dual
union all
select date'2002-2-21', 'com2', 300 from dual
union all
select date'2012-5-4', 'com2', 300 from dual
),
t3 as (
select date'1997-1-1' as fdate, 'com2' as com, 400 as val from dual
union all
select date'1999-9-9', 'com1', 200 from dual
union all
select date'2012-5-20', 'com2', 300 from dual
)
select fdate,com,sum(val1),sum(val2),sum(val3) from (
select fdate,com,val as val1,0 as val2,0 as val3 from t1
union
select fdate,com,0,val,0 from t2
union
select fdate,com,0,0,val from t3
)
group by fdate,comFDATE COM SUM(VAL1) SUM(VAL2) SUM(VAL3)
------------------------- ---- ---------------------- ---------------------- ----------------------
2012-05-04 00:00:00 com2 0 300 0
2012-05-20 00:00:00 com2 0 0 300
1997-01-01 00:00:00 com2 0 0 400
1999-09-09 00:00:00 com1 0 0 200
2004-02-04 00:00:00 com2 300 0 0
2001-01-01 00:00:00 com1 100 0 0
2002-02-21 00:00:00 com2 0 300 0
2004-05-04 00:00:00 com1 300 0 0
1995-01-01 00:00:00 com1 0 300 0
select t3.fdate||t1.fdate||t2.fdate,t1.val,t2.val,t3.val
from t1 full join t2 on t1.fdate=t2.fdate
full join t3 on t1.fdate=t3.fdate
select * from
(
select case when t1.val||t2.val is null then t3.fdate
when t1.val||t3.val is null then t2.fdate
when t2.val||t3.val is null then t1.fdate end fdate,
t1.com||t2.com||t3.com com,t1.val v1,t2.val v2,t3.val v3
from t1 full join t2 on t1.fdate=t2.fdate full join t3 on t1.fdate=t3.fdate
)
order by fdate
select time,company,null as value1,value2,null as value3 from table2 union all
select time,company,null as value1,null as value2,value3 from table3
from (select time,company,valsue as value1,0 as valsue2,0 as value3 from table1
union all
select time,company,0 as valsue1,valsue,0 as value3 from table1
union all
select time,company,0 as valsue1,0 as valsue2,value from table1
)
group by time,company
order by time如果 两个或两以上的表中 在同一个时间,同一个公司都有数据,所以用了group by time,company