쿼리 질문 드립니다 (이전글 조건추가) 0 2 561

by jps [SQL Query] [2020.01.10 16:44:57]


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뎁스 까지만 존재한다는 가정으로 만들었습니다.

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

by 마농 [2020.01.14 08:47:23]
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
UNION ALL SELECT '인사5', '인사3' FROM dual  -- 3 lv 추가
)
, 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
UNION ALL SELECT '인사3', '재직' FROM dual  -- 3 lv 추가
)
, t5 AS
(
SELECT a.cd r_cd
     , b.cnt
     , b.lv
     , c.cd
     , b.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
-- ORDER BY r_cd, cnt, lv
)
SELECT x.item
     , x.v
     , y.cd
     , y.cnt
     , y.lv
     , y.status
     , y.rat
     , (SELECT NVL(SUM(rat), 0)
          FROM t5
         START WITH r_cd = y.r_cd
                AND cnt  = y.cnt
                AND lv   = y.lv
                AND y.status = '재직'
         CONNECT BY PRIOR r_cd = r_cd
                AND PRIOR cnt  = cnt
                AND PRIOR lv+1 = lv
                AND status = '퇴사'
        ) rat_1
  FROM t1 x
     , t5 y
 WHERE x.cd = y.r_cd
 ORDER BY x.item, y.lv
;

 


by jps [2020.01.21 15:05:29]

감사합니다!!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입