将多个子查询结果根据时间建一个虚拟表
例如(#开头的表示字段名)
子查询1:
#D #T #H
2005/10/1 1 3
2005/10/5 3 3
2005/10/7 2 1子查询2:
#D #E #G
2005/10/2 1 3
2005/10/5 5 7
2005/10/6 2 1根据一个时间集如:2005/10/1~2005/10/10,组成一个虚拟表
结果如下:#D #T #H #E #G
2005/10/1 1 3 0 0
2005/10/2 0 0 1 3
2005/10/3 0 0 0 0
2005/10/4 0 0 0 0
2005/10/5 3 3 5 7
2005/10/6 0 0 2 1
2005/10/7 2 1 0 0
2005/10/8 0 0 0 0
2005/10/9 0 0 0 0
2005/10/10 0 0 0 0
例如(#开头的表示字段名)
子查询1:
#D #T #H
2005/10/1 1 3
2005/10/5 3 3
2005/10/7 2 1子查询2:
#D #E #G
2005/10/2 1 3
2005/10/5 5 7
2005/10/6 2 1根据一个时间集如:2005/10/1~2005/10/10,组成一个虚拟表
结果如下:#D #T #H #E #G
2005/10/1 1 3 0 0
2005/10/2 0 0 1 3
2005/10/3 0 0 0 0
2005/10/4 0 0 0 0
2005/10/5 3 3 5 7
2005/10/6 0 0 2 1
2005/10/7 2 1 0 0
2005/10/8 0 0 0 0
2005/10/9 0 0 0 0
2005/10/10 0 0 0 0
from a full outer jion b
where a.#d=b.#d
---------- ---------- ----------
2005/10/01 1 3
2005/10/05 3 3
2005/10/07 2 1crm@152>select to_char(col3, 'yyyy/mm/dd') col3, col4, col44 from test3;COL3 COL4 COL44
---------- ---------- ----------
2005/10/02 1 3
2005/10/05 5 7
2005/10/06 2 1crm@152>select to_char(nvl(t1.col1, t2.col1), 'yyyy/mm/dd') col1, nvl(t1.col2, 0) col2,
2 nvl(t1.col22, 0) col22, nvl(t1.col4, 0) col4, nvl(t1.col44, 0) col44
3 from (
4 select nvl(col1, col3) col1, nvl(col2, 0) col2, nvl(col22, 0) col22,
5 nvl(col4, 0) col4, nvl(col44, 0) col44
6 from test2 t2 full outer join test3 t3 on t2.col1=t3.col3
7 ) t1 full outer join
8 (select to_date('2005-10-1', 'yyyy-mm-dd')+rownum-1 col1
9 from user_tables
10 where rownum<=10
11 ) t2 on t1.col1=t2.col1
12 order by col1;COL1 COL2 COL22 COL4 COL44
---------- ---------- ---------- ---------- ----------
2005/10/01 1 3 0 0
2005/10/02 0 0 1 3
2005/10/03 0 0 0 0
2005/10/04 0 0 0 0
2005/10/05 3 3 5 7
2005/10/06 0 0 2 1
2005/10/07 2 1 0 0
2005/10/08 0 0 0 0
2005/10/09 0 0 0 0
2005/10/10 0 0 0 010 rows selected.
做两次 全连接
select to_char(nvl(t1.col1, t2.col1), 'yyyy/mm/dd') col1, nvl(t1.col2, 0) col2,
nvl(t1.col22, 0) col22, nvl(t1.col4, 0) col4, nvl(t1.col44, 0) col44
from (
select nvl(col1, col3) col1, nvl(col2, 0) col2, nvl(col22, 0) col22,
nvl(col4, 0) col4, nvl(col44, 0) col44
from test2 t2 full outer join test3 t3 on t2.col1=t3.col3
) t1 full outer join
(select to_date('2005-10-1', 'yyyy-mm-dd')+rownum-1 col1
from user_tables
where rownum<=10
) t2 on t1.col1=t2.col1
order by col1;