http://www.gurubee.net/article/81441

이전에 작성한 글입니다.

여기에서 재직여부와 관련된 조건이 추가 됐는데요

자식이 퇴사한 경우에 부모는 자식이 가져가는 몫을 부모가 가지고, 자식은 자신의 몫을 가지지 못합니다.

부모가 퇴사한 경우에 부모는 자신의 몫을 가지지 못하고 자식은 원래의 본인 몫을 가집니다.

이전글에서 답변주신 쿼리를 수정하여 원하는 결과를 만들기는 했는데 너무 꼬아서 작성한것 같기도 하고 더좋은방법이 있지 않을까 해서 글을 올립니다.

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 '아이템5', 500, '인사3' FROM dual
UNION ALL SELECT '아이템6', 600, '인사4' FROM dual
UNION ALL SELECT '아이템7', 1200, '인사5' FROM dual
UNION ALL SELECT '아이템8', 2000, '인사6' FROM dual
)
, t2 AS
(
SELECT '인사1' pcd, '인사2' cd FROM dual
UNION ALL SELECT '인사1', '인사5' FROM dual
UNION ALL SELECT null, '인사1' FROM dual
UNION ALL SELECT null, '인사4' FROM dual
UNION ALL SELECT '인사4', '인사6' 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
)
, t4 AS (
SELECT '인사1' cd, '재직' status FROM dual
UNION ALL SELECT '인사2', '재직' FROM dual
UNION ALL SELECT '인사4', '퇴사' FROM dual
UNION ALL SELECT '인사5', '퇴사' FROM dual
UNION ALL SELECT '인사6', '재직' FROM dual
)
SELECT x.item
     , x.v
     , y.cd
     , y.cnt
     , y.lv
     , y.rat
     , z.status
  FROM t1 x
,(SELECT a.cd r_cd
             , b.cnt
             , b.lv
             , REGEXP_SUBSTR(a.p, '[^-]+', 1, b.lv) cd
       , a.p
       , case when c.status = '퇴사'
    then 0
    when lead(c.status) over(ORDER BY b.cnt, a.p) = '퇴사'
    then case when b.cnt = 2 then lead(b.rat) over(ORDER BY b.cnt, a.p) + b.rat else b.rat end
    else b.rat
    end as rat
       , c.status
    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
       , t4 c
   WHERE a.cnt = b.cnt
     AND REGEXP_SUBSTR(a.p, '[^-]+', 1, b.lv) = c.cd
 ) y
, t4 z
 WHERE x.cd = y.r_cd
   and y.cd = z.cd
 ORDER BY x.item, y.lv

실제 시스템에서 3뎁스인 경우는 현재 없어서 2뎁스 까지만 존재한다는 가정으로 만들었습니다.

더 간단하게 작성하는 방법이 있는지, 혹시 잘못된 부분이 있는지 답변 부탁 드립니다.