求一条sql语句：可以合并上面3个表，变成下面的table123

[darlingdd]

table1
---------------------------------
time company value1
...
2001-1-1 com1 100
2004-2-4 com2 300
2004-5-4 com1 300
...


table2
---------------------------------
time company value2
...
1995-1-1 com1 300
2002-2-21 com2 300
2012-5-4 com2 300
....


table3
---------------------------------
time company value3
...
1997-1-1 com2 400
1999-9-9 com1 200
2012-5-20 com2 300
...

求一条sql语句：可以合并上面3个表，变成下面的table123
要求：
1.要求只用一条sql语句
2.按照日期和公司，将value1,value2,value3合并到同一行
3.按照time排序
截个图

table123
--------------------------------------------
time company value1 value2 value3
...
1995-1-1 com1 300
1997-1-1 com2 400
1999-9-9 com1 200
2001-1-1 com1 100
2002-2-21 com2 300
2004-2-4 com2 300
2004-5-4 com1 300
2012-5-20 com2 300
2012-5-4 com2 300
...

[hupeng213]

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,com

FDATE 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

[HopeInHeart]

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

[billlyh]

[Quote=引用 1 楼 的回复:]
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
)……
[/Quote]
正解

[coolkisses]

最多外层再加个 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,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

[想个好记的名字]

这是个好贴