select a.id,a.value1,b.aid aid1,b.value2,c.aid aid3,c.value3 from a,b,c where a.id=b.aid(+) and a.id=c.aid(+)
select a.*,b.*,c.* from a,b,c where a.id=b.id(+) and a.id=c.id(+)
你的这种写法不对的,如果A1条记录 B 2条记录 C 5条 你这样就是10条 而不只需要产生5条
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(+);
我分析是A表为主 left join 但是B表中有数据 就对应上A表 但是C表有数据还有对应B对应A 如果B没有数据 C表有数据 还要对应上A强!等待高手
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但是效率不行。。咳, 先对付着客户先//感谢各位的关注,结贴了
from a,b,c
where a.id=b.aid(+) and a.id=c.aid(+)
from a,b,c
where a.id=b.id(+)
and a.id=c.id(+)
你的这种写法不对的,如果A1条记录 B 2条记录 C 5条 你这样就是10条 而不只需要产生5条
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(+);
我分析是A表为主
left join
但是B表中有数据 就对应上A表 但是C表有数据还有对应B对应A
如果B没有数据 C表有数据 还要对应上A强!等待高手
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但是效率不行。。咳,
先对付着客户先//感谢各位的关注,结贴了