将两张表多个字段insert到同一张表去

[aoxianglin]

有三个表a,b,c

表a，关系表
cid   oid
001   a
002   b

表b，人员表
cid   cname
001   张三
002   李四

表c，单位表
oid   oname    parentid(上级)   type 
a      地区1     aa              1
b      地区2     bb              1
aa     地区3     aaa             1
aaa    地区4     aaaa            2
bb     地区5     bbb             2

现在要把数据写到d表去，根据关系表的关联，
不过，单位表有上下级关系，从本级开始往上查找上级单位，找到第一个type=2的单位就把记录写到d表（如果与人员关系的单位type=2，则直接取这个单位。），就是取一个与子节点最近的type=2的父节点

表d
cid  cname  oid  oname
001   张三   aaa  地区4
002   李四   bb   地区5

[UPC子夜]

insert into d
(cid, cname, oid, oname)
select b.cid,
b.cname,
(select C.oid
from c C
where C.type = 2
and rownum = 1
connect by prior C.parentid = C.oid
start with with
C.oid = (select A.oid from a A where A.cid = B.cid)) as oid,
(select C.oname
from c C
where C.type = 2
and rownum = 1
connect by prior C.parentid = C.oid
start with with
C.oid = (select A.oid from a A where A.cid = B.cid)) as oname
from b B

[aoxianglin]

insert into d
  (cid, cname, oid, oname)
  (select f.cid, b.cname, f.oid, c.oname
     from b,
          c,
          (select b.cid,
                  (select to_char(substr(min(level || oid), 2))
                     from c
                    start with oid = a.oid
                   connect by oid = prior parentid) oid
             from a) f
    where b.cid = f.cid
      and c.oid = f.oid);

自己搞了下