데이터베이스 질문과 답변
쿼리 짜던 도중 계층쿼리를 사용하여
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)
표처럼 출력이 되어야 합니다. 도와주세요..
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
;
마농님 덕분에 해결 되었습니다. 스칼라 서브의 비효율적을 이제야 이해 되었네요
감사합니다. 마농님
) 버튼을 클릭하여 작성 하시면 됩니다.