oracle 连接查询一对多，多对多字段一对一随机绑定解决方案。

oracle 连接查询一对多，多对多字段一对一随机绑定解决方案。

利用Oracle中 row_number() voer() 给需要连接的表 分组排序 连接查询时 on 条件加上排序值。 若有误，望指正。
代码如下：

select nvl(t.id, t1.id) as id,
t.name as name,
t1.age as age,
(case
when t.id = t1.id then
‘00’
when t.id is null then --T 表中不存在
‘01’
when t1.id is null then --T1表不存在
‘02’
end) as status
from (select t., row_number() over(partition by id order by name) rn
from (select ‘1’ as id, ‘张三’ as name
from dual
union all
select ‘2’ as id, ‘李四’ as name
from dual
union all
select ‘2’ as id, ‘王麻子’ as name
from dual
union all
select ‘3’ as id, ‘王五’ as name
from dual
union all
select ‘4’ as id, ‘赵六’ as name
from dual
union all
select ‘6’ as id, ‘李七’ as name
from dual
union all
select ‘7’ as id, ‘吴忘’ as name
from dual) t) t
full join (select t., row_number() over(partition by id order by age) rn
from (select ‘1’ as id, ‘18’ as age
from dual
union all
select ‘2’ as id, ‘20’ as age
from dual
union all
select ‘2’ as id, ‘18’ as age
from dual
union all
select ‘2’ as id, ‘30’ as age
from dual
union all
select ‘3’ as id, ‘15’ as age
from dual
union all
select ‘4’ as id, ‘17’ as age
from dual
union all
select ‘5’ as id, ‘20’ as age
from dual) t) t1
on t.id = t1.id
and t.rn = t1.rn