表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?
解决方案 »
- 行列转换应该怎么写
- ora-01034 ora-27101
- dba_snapshotS中的意思?
- 不知道表的结构,如何操作表,并按照一定的顺序显示表里的记录。
- 怎么导出oracle中某个用户下的所有对象(包括表、存储过程、视图、触发器、序列、包等)
- Oracle 9i Database links 10g
- 急:oracle临时表空间使用率为零
- 请问谁要ORACLE10G和9I的最新的在LINUX下WINDOWS下的全套补丁(尤其是最大的那4个几百兆的),请回贴
- 请问能否在ORACLE中实现下面的功能?(急!想要多少分,我尽力给)
- 小弟请教各位大侠:关于Unix下Oracle调用外部函数的问题
- 小弟最近在学习ORACLE ,从官网上上下载了ORACLE 11,没有注册码,不注册可以吗?
- oracle 创建监听却连不上???急急急
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