17,086
社区成员
发帖
与我相关
我的任务
分享
A B C
ID value1 AID value2 AID value3
1 abc 1 a1 1 a11
2 aaa 1 a2 2 a22
3 ddd 2 b1 2 a33
3 c1 3 a44
1 abc 1 a1 1 a11
1 abc 1 a2
2 aaa 2 b1 2 a22
2 aaa 2 a33
3 ddd 3 c1 3 a44
select t1.*,t2.* from a full outer join ( with t2 as (select idb id_b, value2 ,row_number() over(partition by idb order by idb asc) rn2 from b),
t3 as (select idc id_c, partcode ,row_number() over(partition by idc order by idc asc) rn3 from c)
select t2.*,t3.*
from t3 full outer join t2 on t2.id_b = t3.id_c and t2.rn2 = t3.rn3 ) t2 on t1.id= t2.id_b or t1.id = t3.id_c
with t1 as (select id id_a, value1 ,row_number()over(partition by id order by value1 asc) rn1 from a),
t2 as (select aid id_b, value2 ,row_number()over(partition by aid order by value2 asc) rn2 from b),
t3 as (select aid id_c, value3 ,row_number()over(partition by aid order by value3 asc) rn3 from c)
select t1.id_a,value1,t2.id_b,value2,t3.id_c,value3
from t1,t2,t3
where t1.id_a=t2.id_b(+) and t1.id_a=t3.id_c(+) and t1.rn1=t2.rn2(+) and t1.rn1=t3.rn3(+);
select a.*,b.*,c.*
from a,b,c
where a.id=b.id(+)
and a.id=c.id(+)