쿼리 질문 드립니다. 0 2 81

by 초봉 [SQL Query] [2017.12.05 18:24:16]


쿼리 짜던 도중 계층쿼리를 사용하여

sum 하는 도중

키가 같은 코드 지만 상위코드는 다른 경우가 발생하였습니다.

그런데 계층으로 up 하여 sum  이안되어 이렇게 질문 드립니다.

강감찬 이라는 사람이 인사팀에도 존재하고 총무팀에도 존재합니다.

DEPT UPDEPT LEV BUDAMT EXEAMT 사용가능액
본부   1 3000 4000 -1000
센터 본부 2 1000 1500 -500
인사팀 센터 3 1000 1500 -500
강감찬 인사팀 4   1000  
홍길동 인사팀 4   500  
총무팀 본부 2 2000 2500 -500
강감찬 총무팀 3   500  
공찬 총무팀 3   1000  
이순신 총무팀 3   1000  

 

/* Formatted on 2017/12/05 오후 6:22:19 (QP5 v5.256.13226.35510) */
WITH T
     AS (SELECT '본부' DEPT
               ,NULL UPDEPT
               ,1 LEV
               ,0 BUDAMT
               ,0 EXEAMT
           FROM DUAL
         UNION ALL
         SELECT '센터'
               ,'본부'
               ,2
               ,0
               ,0
           FROM DUAL
         UNION ALL
         SELECT '인사팀'
               ,'센터'
               ,3
               ,1000
               ,0
           FROM DUAL
         UNION ALL
         SELECT '총무팀'
               ,'본부'
               ,2
               ,2000
               ,0
           FROM DUAL
         UNION ALL
         SELECT '홍길동'
               ,'인사팀'
               ,4
               ,0
               ,500
           FROM DUAL
         UNION ALL
         SELECT '강감찬'
               ,'인사팀'
               ,4
               ,0
               ,500
           FROM DUAL
         UNION ALL
         SELECT '이순신'
               ,'총무팀'
               ,3
               ,0
               ,1000
           FROM DUAL
         UNION ALL
         SELECT '공찬'
               ,'총무팀'
               ,3
               ,0
               ,1000
           FROM DUAL
         UNION ALL
         SELECT '강감찬'
               ,'총무팀'
               ,4
               ,0
               ,500
           FROM DUAL)
SELECT DEPT
      ,UPDEPT
      ,LEV
      ,BUDAMT
      ,EXEAMT
      ,DECODE (
          LEAF
         ,1, ''
         ,    EXEAMT
          - BUDAMT
       )
          AS "사용가능액"
  FROM (    SELECT DEPT
                  ,UPDEPT
                  ,LEV
                  , (     SELECT SUM (BUDAMT)
                           FROM T
                     START WITH DEPT = A.DEPT
                     CONNECT BY PRIOR DEPT = UPDEPT)
                      AS BUDAMT
                  , (     SELECT SUM (EXEAMT)
                           FROM T
                     START WITH DEPT = A.DEPT
                     CONNECT BY PRIOR DEPT = UPDEPT)
                      EXEAMT
                  ,CONNECT_BY_ISLEAF AS LEAF
              FROM T A
        START WITH DEPT = '본부'
        CONNECT BY PRIOR DEPT = UPDEPT)

표처럼 출력이 되어야 합니다. 도와주세요..

 

 

 

 

by 마농 [2017.12.05 18:56:53]

1. 스칼라서브쿼리의 START WITH 절 조건 오류(두군데)
  - 시작조건에 중복이 발생되겠네요.
  - 중복이 발생되지 않도록 조건 추가해 주세요.
  - AND NVL(UPDEPT, 'x') = NVL(a.UPDEPT, 'x')
2. 뺄셈 오류
  - 거꾸로 뺐네요.
3. 스칼라서브쿼리는
  - 매행마다 반복 수행되므로 비효율입니다.
  - 더군다나 합계 항목이 두개라서 두번 써야 하죠.
  - 일전 질문에 스칼라서브쿼리 이용하지 않는 방법 답변달았었는데요.
  - 그거 이용해 보세요.
  - 여기서도 마찬가지로 updept 조건이 추가되어야 합니다.
 

WITH t AS 
(
SELECT '본부' dept, NULL updept, 1 lev, 0 budamt, 0 exeamt FROM dual
UNION ALL SELECT '센터'  , '본부'  , 2,    0,    0 FROM dual
UNION ALL SELECT '인사팀', '센터'  , 3, 1000,    0 FROM dual
UNION ALL SELECT '총무팀', '본부'  , 2, 2000,    0 FROM dual
UNION ALL SELECT '홍길동', '인사팀', 4,    0,  500 FROM dual
UNION ALL SELECT '강감찬', '인사팀', 4,    0,  500 FROM dual
UNION ALL SELECT '이순신', '총무팀', 3,    0, 1000 FROM dual
UNION ALL SELECT '공찬'  , '총무팀', 3,    0, 1000 FROM dual
UNION ALL SELECT '강감찬', '총무팀', 4,    0,  500 FROM dual
)
SELECT a.rn
     , a.dept
     , a.updept
     , a.lev
     , b.budamt
     , b.exeamt
     , DECODE(a.leaf, 0, b.budamt - b.exeamt) x
  FROM (-- 1. 정렬용 계층 쿼리 --
        SELECT ROWNUM rn
             , dept, updept, lev
             , CONNECT_BY_ISLEAF leaf
          FROM t
         START WITH updept IS NULL
         CONNECT BY PRIOR dept = updept
        ) a
     , (-- 2. 합계용 계층 쿼리 --
        SELECT dept
             , updept
             , SUM(budamt) budamt
             , SUM(exeamt) exeamt
          FROM (SELECT CONNECT_BY_ROOT(dept) dept
                     , CONNECT_BY_ROOT(updept) updept
                     , budamt
                     , exeamt
                  FROM t
                 CONNECT BY PRIOR dept = updept
                )
         GROUP BY dept, updept
        ) b
 WHERE a.dept = b.dept
   AND NVL(a.updept, 'x') = NVL(b.updept, 'x')
 ORDER BY rn
;

 


by 초봉 [2017.12.05 20:24:35]

마농님 덕분에 해결 되었습니다. 스칼라 서브의 비효율적을 이제야 이해 되었네요

감사합니다. 마농님

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