解决方案 »
- oracle中的schema究竟是个什么东西?
- 我安装oracle 9i的时候提示系统找不到IP地址
- 一个最基本的问题,connect <username> [as sysdba|sysoper]
- Oracle字符集问题(巨 难),高手请进!………………………………………………
- 请问oracle8i支持java存储过程吗?
- [50分]菜鸟--简单--问题急用在线等!
- 在ORACLE 有没有像SQL SERVER里的xp_cmdshell 的命令来启动WINDOWS里的执行文件吗
- 不能创建视图?
- 文本文件(*.txt)中的数据可以导入到oracle数据库中么,怎么实现的??急急急
- 高手救我呀,这个触发器有点难!!高手不觉得难。
- pl/sql 如何读取csv文件~~~
- oracle 唯一约束,急急急!!!!!!
如果真的满足这样,就是full join了
得到的结果也不是楼主给的比如A表的2
2 aa bb b
3 aa aa c
跑哪去了
给个full join的select a1,b,c,a2,x,y,a.status
from a
full join b on a.a1=b.a2
wher a.status=a
and b.status=a
;
这样做得不到这些数据 “左表中存在但右表中不存在的数据,右表中存在,但左表中不存在的数据”
比如像B表中的这条 4 33 22 a
SELECT 1 a1,'aa' b,'bb' c,'a' status FROM dual UNION ALL
SELECT 2, 'aa', 'bb', 'b' FROM dual UNION ALL
SELECT 3, 'aa', 'aa', 'c' FROM dual UNION ALL
SELECT 5, 'aa', 'aa', 'a' FROM dual
),
tab2 AS(
SELECT 1 a2,'11' x, '11' y, 'a' status FROM dual UNION ALL
SELECT 4 ,'33' ,'22' ,'a' FROM dual UNION ALL
SELECT 3, 'aa', 'bb','d' FROM dual
)
SELECT a.* ,b.* FROM
(SELECT * FROM tab1 WHERE status='a') a
full outer join
(SELECT * FROM tab2 WHERE status='a') b
on a.a1=b.a2 ;
结果:
A1 B C STATUS A2 X Y STATUS
-------------------------------
1 aa bb a 1 11 11 a
5 aa aa a
4 33 22 a
已写入 file afiedt.buf 1 with tb1 as(
2 SELECT 1 a1,'aa' b,'bb' c,'a' status from dual union all
3 SELECT 2, 'aa', 'bb', 'b' from dual union all
4 SELECT 3, 'aa', 'aa', 'c' from dual union all
5 SELECT 5, 'aa', 'aa', 'a' from dual
6 ),
7 tb2 as(
8 SELECT 1 a2,'11' x, '11' y, 'a' status from dual union all
9 SELECT 4 ,'33' ,'22' ,'a' from dual union all
10 SELECT 3, 'aa', 'bb','d' from dual
11 )
12 select a1,b,c,a2,x,y,status
13 from
14 (select a1,b,c,a2,x,y,b.status
15 from tb1 a,tb2 b
16 where a.a1(+)=b.a2
17 union
18 select a1,b,c,a2,x,y,a.status
19 from tb1 a,tb2 b
20 where a.a1=b.a2(+)) t
21* where t.status='a'
SQL> / A1 B C A2 X Y S
---------- -- -- ---------- -- -- -
1 aa bb 1 11 11 a
5 aa aa a
4 33 22 a已用时间: 00: 00: 00.07
SELECT 1 a1,'aa' b,'bb' c,'a' status FROM dual UNION ALL
SELECT 2, 'aa', 'bb', 'b' FROM dual UNION ALL
SELECT 3, 'aa', 'aa', 'c' FROM dual UNION ALL
SELECT 5, 'aa', 'aa', 'a' FROM dual
),
tab2 AS(
SELECT 1 a2,'11' x, '11' y, 'a' status FROM dual UNION ALL
SELECT 4 ,'33' ,'22' ,'a' FROM dual UNION ALL
SELECT 3, 'aa', 'bb','d' FROM dual
)
SELECT tab1.a1,tab1.b,tab1.c,tab2.a2,tab2.x,tab2.y,'a' status FROM tab1 FULL JOIN tab2 ON tab1.a1=tab2.a2
WHERE (tab1.status='a' AND (tab2.status='a' OR tab2.status IS NULL))
OR (tab2.status='a' AND (tab1.status='a' OR tab1.status IS NULL));
select '2' a1,'aa' b ,'bb' c,'b' status from dual union all
select '3' a1,'aa' b ,'aa' c,'c' status from dual union all
select '5' a1,'aa' b ,'aa' c,'a' status from dual
#,
tab2 as # select '1' a2,'aa' x ,'bb' y,'a' status from dual union all
select '4' a2,'aa' x ,'bb' y,'a' status from dual union all
select '3' a2,'aa' x ,'aa' y,'d' status from dual
#
select tab1#a1,tab1#b,tab1#c,tab2#a2,tab2#x,tab2#y,nvl#tab1#status,tab2#status#
from tab1 full join tab2 on tab1#a1= tab2#a2 where tab1#status='a' or tab2#status='a'
order by nvl#tab1.a1,tab2.a2#
select '2' a1,'aa' b ,'bb' c,'b' status from dual union all
select '3' a1,'aa' b ,'aa' c,'c' status from dual union all
select '5' a1,'aa' b ,'aa' c,'a' status from dual
),
tab2 as ( select '1' a2,'aa' x ,'bb' y,'a' status from dual union all
select '4' a2,'aa' x ,'bb' y,'a' status from dual union all
select '3' a2,'aa' x ,'aa' y,'d' status from dual
)
select tab1.a1,tab1.b,tab1.c,tab2.a2,tab2.x,tab2.y,nvl(tab1.status,tab2.status)
from tab1 full join tab2 on tab1.a1= tab2.a2 where tab1.status='a' or tab2.status='a'
order by nvl(tab1.a1,tab2.a2)
(
ID varchar2(10),
CI_1 varchar2(15),
CI_2 varchar2(15),
status CHAR(1)
)Create table TEST2
(
ID varchar2(10),
CI_1 varchar2(15),
CI_2 varchar2(15),
status CHAR(1)
)
insert into TEST1 values (1,11,11,'a');
insert into TEST1 values (2,22,22,'a');
insert into TEST2 values (1,'aa','a','a');
insert into TEST2 values (4,'bb','bb','a');
执行查询:
select t1.id ,t1.ci_1,t1.ci_2,t2.id,t2.ci_1,t2.ci_2,nvl(t1.status,t2.status) status
from test1 t1,test2 t2
where t1.id = t2.id(+)
and t1.status = t2.status(+)
and t1.status = 'a'
union
select t1.id ,t1.ci_1,t1.ci_2,t2.id,t2.ci_1,t2.ci_2,nvl(t1.status,t2.status) status
from test1 t1,test2 t2
where t1.id(+) = t2.id
and t1.status(+) = t2.status
and t2.status = 'a'OK