然后取出,两表中共同存在的数据、左表中存在但右表中不存在的数据,右表中存在,但左表中不存在的数据。 如果真的满足这样,就是full join了 得到的结果也不是楼主给的比如A表的2 2 aa bb b 3 aa aa c 跑哪去了
再加上状态限定条件:是不是status=a 给个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 ;
to canhui 这样做得不到这些数据 “左表中存在但右表中不存在的数据,右表中存在,但左表中不存在的数据” 比如像B表中的这条 4 33 22 a
WITH tab1 AS( 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
SQL> edi 已写入 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
呃,我写得好傻WITH tab1 AS( 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));
with tab1 as ( select '1' a1,'aa' b ,'bb' c,'a' status from dual union all 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#
with tab1 as ( select '1' a1,'aa' b ,'bb' c,'a' status from dual union all 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)
是不是这个意思?Create table TEST1 ( 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
如果真的满足这样,就是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