WITH t1 AS
(
SELECT '아이템1' item, 100 v, '인사1' cd FROM dual
UNION ALL SELECT '아이템2', 200, '인사1' FROM dual
UNION ALL SELECT '아이템3', 300, '인사2' FROM dual
UNION ALL SELECT '아이템4', 400, '인사3' FROM dual
UNION ALL SELECT '아이템5', 500, '인사3' FROM dual
UNION ALL SELECT '아이템6', 600, '인사4' FROM dual
)
, t2 AS
(
SELECT '인사1' pcd, '인사2' cd FROM dual
UNION ALL SELECT '인사2', '인사3' FROM dual
UNION ALL SELECT null, '인사1' FROM dual
UNION ALL SELECT null, '인사4' FROM dual
)
, t3 AS
(
SELECT 1 cnt, 1 lv, 100 rat FROM dual
UNION ALL SELECT 2, 1, 20 FROM dual
UNION ALL SELECT 2, 2, 80 FROM dual
UNION ALL SELECT 3, 1, 10 FROM dual
UNION ALL SELECT 3, 2, 40 FROM dual
UNION ALL SELECT 3, 3, 50 FROM dual
)
SELECT x.item
     , x.v
     , y.cd
     , y.cnt
     , y.lv
     , y.rat
  FROM t1 x
     , (SELECT a.cd r_cd
             , b.cnt
             , b.lv
             , REGEXP_SUBSTR(a.p, '[^-]+', 1, b.lv) cd
             , b.rat
          FROM (SELECT pcd, cd, LEVEL cnt
                     , SYS_CONNECT_BY_PATH(cd, '-') p
                  FROM t2
                 START WITH pcd IS NULL
                 CONNECT BY PRIOR cd = pcd
                ) a
             , t3 b
         WHERE a.cnt = b.cnt
        ) y
 WHERE x.cd = y.r_cd
 ORDER BY x.item, y.lv
;

정말 감사합니다!!