key假设A(a1,a2),B(b1)-->通过a1=b1关联;A还有字段a3,B还有字段b2 select a1,a2,a3,decode(rn,1,b2,null) from( select row_number() over (partition by a1) as rn,* ( select x.a1,x.a2,x.a3,y.b2 from A x,B y where x.a1=y.b1(+)) )
SQL> select A.akey1, 2 A.akey2, 3 A.afield1, 4 A.afield2, 5 B.bkey1, 6 decode(max(A.akey2) over(partition by A.akey1,B.bkey1),A.akey2,B.bfield1,null) as bfield1, 7 decode(max(A.akey2) over(partition by A.akey1,B.bkey1),A.akey2,B.bfield2,null) as bfield2 8 from (select 2 as akey1,1 as akey2,'aaa' as afield1,'111a' as afield2 from dual 9 union all 10 select 1 as akey1,2 as akey2,'bbb' as afield1,'111b' as afield2 from dual 11 union all 12 select 3 as akey1,3 as akey2,'ccc' as afield1,'111c' as afield2 from dual 13 union all 14 select 2 as akey1,4 as akey2,'ddd' as afield1,'111d' as afield2 from dual 15 union all 16 select 3 as akey1,5 as akey2,'eee' as afield1,'111e' as afield2 from dual 17 )A, 18 ( 19 select 1 as bkey1,'a111' as bfield1,'ab111' as bfield2 from dual 20 union all 21 select 2 as bkey1,'b111' as bfield1,'bb111' as bfield2 from dual 22 union all 23 select 3 as bkey1,'c111' as bfield1,'cb111' as bfield2 from dual 24 )B 25 where A.akey1 = B.bkey1; AKEY1 AKEY2 AFIELD1 AFIELD2 BKEY1 BFIELD1 BFIELD2 ---------- ---------- ------- ------- ---------- ------- ------- 1 2 bbb 111b 1 a111 ab111 2 1 aaa 111a 2 2 4 ddd 111d 2 b111 bb111 3 3 ccc 111c 3 3 5 eee 111e 3 c111 cb111
select a1,a2,a3,decode(rn,1,b2,null) from(
select row_number() over (partition by a1) as rn,*
( select x.a1,x.a2,x.a3,y.b2 from A x,B y where x.a1=y.b1(+))
)
2 A.akey2,
3 A.afield1,
4 A.afield2,
5 B.bkey1,
6 decode(max(A.akey2) over(partition by A.akey1,B.bkey1),A.akey2,B.bfield1,null) as bfield1,
7 decode(max(A.akey2) over(partition by A.akey1,B.bkey1),A.akey2,B.bfield2,null) as bfield2
8 from (select 2 as akey1,1 as akey2,'aaa' as afield1,'111a' as afield2 from dual
9 union all
10 select 1 as akey1,2 as akey2,'bbb' as afield1,'111b' as afield2 from dual
11 union all
12 select 3 as akey1,3 as akey2,'ccc' as afield1,'111c' as afield2 from dual
13 union all
14 select 2 as akey1,4 as akey2,'ddd' as afield1,'111d' as afield2 from dual
15 union all
16 select 3 as akey1,5 as akey2,'eee' as afield1,'111e' as afield2 from dual
17 )A,
18 (
19 select 1 as bkey1,'a111' as bfield1,'ab111' as bfield2 from dual
20 union all
21 select 2 as bkey1,'b111' as bfield1,'bb111' as bfield2 from dual
22 union all
23 select 3 as bkey1,'c111' as bfield1,'cb111' as bfield2 from dual
24 )B
25 where A.akey1 = B.bkey1; AKEY1 AKEY2 AFIELD1 AFIELD2 BKEY1 BFIELD1 BFIELD2
---------- ---------- ------- ------- ---------- ------- -------
1 2 bbb 111b 1 a111 ab111
2 1 aaa 111a 2
2 4 ddd 111d 2 b111 bb111
3 3 ccc 111c 3
3 5 eee 111e 3 c111 cb111