쿼리 질문 드립니다 0 2 1,167

by jps [SQL Query] [2019.06.03 02:08:45]


아이템코드 인사코드
아이템1 100 인사1
아이템2 200 인사1
아이템3 300 인사2
아이템4 400 인사3
아이템5 500 인사3
아이템6 600 인사4

각 아이템 마다 값과 해당 소유자 정보가 있는 테이블이 있고 

 

상급자코드 인사코드
인사1 인사2
인사2 인사3
null 인사1
null 인사4

직원들의 계층구조가 표현된 테이블도 있습니다.

이 계층구조에 따라서 부하직원이 갖고 있는 아이템의 값을 미리 설정된 비율로 상급자와 나눠 갖는 결과를 만들고 싶습니다.

여기에서의 예시 비율은 2명이 나눌시 부모 20%, 자식 80%, 3명이 나눌시 조부모 10%, 부모 40%, 자식 50% 이고 나눌 부모가 없는 경우(인사1, 인사4) 에는 100% 입니다.

이 비율을 설정해 놓을 테이블도 있어야 할것 같은데 구조를 어떻게 만들어야 될지 모르겠습니다.

 

만들고 싶은 결과는 아래와 같습니다.

아이템코드 인사코드 비율
아이템1 100 인사1 100
아이템2 200 인사1 100
아이템3 300 인사1 20
아이템3 300 인사2 80
아이템4 400 인사1 10
아이템4 400 인사2 40
아이템4 400 인사3 50
아이템5 500 인사1 10
아이템5 500 인사2 40
아이템5 500 인사3 50
아이템6 600 인사4 100

이렇게 나눠갖는 사람수 만큼 행을 생성하고 나눠갖는 사람의 수에따라 정해놓은 비율까지 한줄에 출력하고 싶은데 방법이 떠오르지 않아 질문 드립니다.

도움 요청 드립니다.

by 마농 [2019.06.03 10:45:57]
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
;

 


by jps [2019.06.18 21:29:22]

정말 감사합니다!!

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