表A
A_DATE A_VALUE
2010-10-12 55表B
B_DATE B_VALUE
2010-10-11 46期望得到两个表合并后的记录集
DATE A_VALUE B_VALUE
2010-10-12 55 NULL
2010-10-11 NULL 46
请教高手如何写sql?
A_DATE A_VALUE
2010-10-12 55表B
B_DATE B_VALUE
2010-10-11 46期望得到两个表合并后的记录集
DATE A_VALUE B_VALUE
2010-10-12 55 NULL
2010-10-11 NULL 46
请教高手如何写sql?
Connected as SYSSQL>
SQL> with tableA as
2 (
3 select '2010-10-12' a_date,55 a_value from dual
4 )
5 , tableB as
6 (
7 select '2010-10-11' b_date,46 b_value from dual
8 )
9 select a.a_date,nvl(a.a_value,null),nvl(b.b_value,null) from tableA a,tableB b where a.a_date=b.b_date(+)
10 union all
11 select b.b_date,nvl(a.a_value,null),nvl(b.b_value,null) from tableA a,tableB b where a.a_date(+)=b.b_date;A_DATE NVL(A.A_VALUE,NULL) NVL(B.B_VALUE,NULL)
---------- ------------------- -------------------
2010-10-12 55
2010-10-11 46SQL>
create table ta(a_date date,a_value number(10));
create table tb(b_date date,b_value number(10));insert into ta values(to_datE('2010-10-12','YYYY-MM-DD'),55);
insert into tb values(to_date('2010-10-11','YYYY-MM-DD'),46);select a.a_date as data,a.a_value,null as b_value from ta a
union
select b.b_date as data ,null as a_value,b.b_value from tb b;
Connected as SYSSQL>
SQL> with tableA as
2 (
3 select '2010-10-12' a_date,55 a_value from dual
4 union all
5 select '2010-10-11' ,46 from dual
6 )
7 , tableB as
8 (
9 select '2010-10-11' b_date,46 b_value from dual
10 )
11 select a.a_date, nvl(a.a_value, null), nvl(b.b_value, null)
12 from tableA a, tableB b
13 where a.a_date = b.b_date(+)
14 union
15 select b.b_date, nvl(a.a_value, null), nvl(b.b_value, null)
16 from tableA a, tableB b
17 where a.a_date(+) = b.b_date;A_DATE NVL(A.A_VALUE,NULL) NVL(B.B_VALUE,NULL)
---------- ------------------- -------------------
2010-10-11 46 46
2010-10-12 55 SQL>
select a.a_date,a.a_value,b.b_value from a full join b on a.a_date = b.b_date;
--value 字段为number类型
select a.id,a.a_value,
case when b.b_value is null then 0 end b_value
from a left join b on a.id=b.id
union
select b.id,
case when a.a_value is null then 0 end a_value
,b.b_value from a right join b on a.id=b.id
--value 字段为 varchar 类型
select a.id,a.a_value,
case when b.b_value is null then 'null' end b_value
from a left join b on a.id=b.id
union
select b.id,
case when a.a_value is null then 'null' end a_value
,b.b_value from a right join b on a.id=b.id
union all
select B_DATE DATE, '' A_VALUE, B_VALUE from A;
union all
select B_DATE DATE, '' A_VALUE, B_VALUE from B;
on a.date=b.date
union --不去重加个all
select B_DATE,null,B_VALUE from B