两个表
表一
A B C
a1 b1 c1
a1 b2 c2
a1 b3 c3
a2 b2 c1
a2 b4 c4
表二
A D
a1 d1
a1 d2
a2 d3
需要得到的结果
A B C D
a1 b1 c1 d1
a1 b2 c2 d2
a1 b3 c3
a2 b2 c1 d3
a2 b4 c4
------解决方案--------------------
select nvl(a.a,b.a),a.b,a.c,b.d
from
(select *,row_number()over(partition by a order by a) as num from a) a
full join
(select *,row_number()over(partition by a order by a) as num from b) b
on a.a=b.a and a.num=b.num
------解决方案--------------------
SELECT nvl(a.a,b.a)a,a.b,a.c,b.d
FROM (SELECT table1.*,row_number()OVER(PARTITION BY A ORDER BY b)rn FROM table1)A
FULL JOIN(SELECT table2.*,row_number()OVER(PARTITION BY A ORDER BY d)rn FROM table2)b
on a.a=b.a and a.rn=b.rn
------解决方案--------------------
- SQL code
with table1 as( select 'a1' A,'b1' B, 'c1' C from dual union all select 'a1' A,'b2' B, 'c2' C from dual union all select 'a1' A,'b3' B, 'c3' C from dual union all select 'a2' A,'b2' B, 'c1' C from dual union all select 'a2' A,'b4' B, 'c4' C from dual), table2 as( select 'a1' A, 'd1' D from dual union all select 'a1' A, 'd2' D from dual union all select 'a2' A, 'd3' D from dual) SELECT t1.A, t1.B, t1.C, t2.D FROM (SELECT t.*, row_number() over(PARTITION BY A ORDER BY A, B, C) rn FROM table1 t) t1, (SELECT t.*, row_number() over(PARTITION BY A ORDER BY A, D) rn FROM table2 t) t2 WHERE t1.A = t2.A(+) AND t1.rn = t2.rn(+) ORDER BY t1.A, t1.B, t1.C;