问题:
现有表A 字段 a,b。库里记录为:
a b
2009-12-12 2
2009-12-16 3
2009-12-17 4
2009-12-18 8表B 字段 c,d。库里记录为:
c d
2009-12-09 1
2009-12-10 2
2009-12-17 2
2009-12-18 7需求是将两个表合并成一个表C,记录格式为:
a b d
2009-12-09 0 1
2009-12-10 0 2
2009-12-12 2 0
2009-12-16 3 0
2009-12-17 4 2
2009-12-18 8 7求高手解答,谢谢。
现有表A 字段 a,b。库里记录为:
a b
2009-12-12 2
2009-12-16 3
2009-12-17 4
2009-12-18 8表B 字段 c,d。库里记录为:
c d
2009-12-09 1
2009-12-10 2
2009-12-17 2
2009-12-18 7需求是将两个表合并成一个表C,记录格式为:
a b d
2009-12-09 0 1
2009-12-10 0 2
2009-12-12 2 0
2009-12-16 3 0
2009-12-17 4 2
2009-12-18 8 7求高手解答,谢谢。
SQL> with t as
2 (
3 select date '2009-12-12' a ,2 b from dual
4 union all
5 select date '2009-12-16' ,3 from dual
6 union all
7 select date'2009-12-17', 4 from dual
8 union all
9 select date'2009-12-18', 8 from dual
10 ),
11 t1 as(
12 select date'2009-12-09' c,1 d from dual
13 union all
14 select date'2009-12-10', 2 from dual
15 union all
16 select date'2009-12-17', 2 from dual
17 union all
18 select date'2009-12-18', 7 from dual)
19 select nvl(a,c) a,nvl(b,0) b,nvl(d,0) d from t full outer join t1 on t.a=t1.c
20 order by a;
A B D
----------- ---------- ----------
2009/12/9 0 1
2009/12/10 0 2
2009/12/12 2 0
2009/12/16 3 0
2009/12/17 4 2
2009/12/18 8 7
6 rows selected
(select A AS A,B,0 AS D FROM A
UNION ALL
SELECT C AS A,0 AS B,D AS D FROM B)
GROUP BYY A
create table A (
a VARCHAR2(20) NOT NULL,
b VARCHAR2(20) NOT NULL,
);create table B (
c VARCHAR2(20) NOT NULL,
d VARCHAR2(20) NOT NULL,
);insert into A (a, b) values ('2009-12-12','2');
insert into A (a, b) values ('2009-12-16','3');
insert into A (a, b) values ('2009-12-17','4');
insert into A (a, b) values ('2009-12-18','8');
insert into B (c, d) values ('2009-12-09','1');
insert into B (c, d) values ('2009-12-10','2');
insert into B (c, d) values ('2009-12-17','2');
insert into B (c, d) values ('2009-12-18','7');
commit;以上是测试数据。
-------------------- -------------------- --------------------
2009-12-09 0 1
2009-12-10 0 2
2009-12-12 2 0
2009-12-16 3 0
2009-12-17 4 2
2009-12-18 8 76 rows selectedSQL>
SQL> select nvl(a,c) a,nvl(b,0) b,nvl(d,0) d from a full outer join b on a.a=b.c
2 order by a;
A B D
-------------------- -------------------- --------------------
2009-12-09 0 1
2009-12-10 0 2
2009-12-12 2 0
2009-12-16 3 0
2009-12-17 4 2
2009-12-18 8 7
6 rows selected
(select A AS A,B,'0' AS D FROM A
UNION ALL
SELECT C AS A,'0' AS B,D AS D FROM B)
GROUP BY A
稍微改下就可以了