데이터베이스 질문과 답변
by 앗싸 [2018.04.11 15:44:44]
계층형 쿼리 관련 질문 좀 드립니다.
1. 부서 Table
--------------------------------------
부서코드 부서명 부모코드
1 (주)OOO회사 0
2 지원실 1
3 지원1팀 2
4 지원2팀 2
5 기획실 1
6 기획1팀 5
7 기획2팀 5
2. 급여 Table
-------------------------------------------------------------
년월 부서코드 성명 급여
201801 2 홍길동 100 (지원실)
201801 3 이름2 50 (지원1팀)
201801 4 이름3 80 (지원2팀)
201801 6 이름4 90 (기획1팀)
원하는 결과 값
- 두개 테이블을 계층형 쿼리로 조인하여 아래 결과 값을 얻고자 합니다.
- 궁금한 것은, 급여 table 에 기획실이 없어도 기획1팀 데이타가 있기 때문에 상위 부서 기획실은 조회 되게 하려고합니다.
가능한지요??
------------------------------------------------------------------------------------
(주)OOO회사
지원실
재무팀
총무팀
기획실 (급여테이블에는 데이터가 없으나 기획1팀 데이타가 급여 테이블에 있으니 상위 부서인 기획실은 조회 되게 하고 싶습니다.)
기획1팀
결과가 모호하네요.
원하시는 결과를 명확하게 값을 포함한 표형태로 보여주세요.
WITH ORG AS (
SELECT 1 AS code, '(주)ㅇㅇ회사' AS org_name, 0 parent_code FROM DUAL
UNION ALL SELECT 2, '지원실', 1 FROM DUAL
UNION ALL SELECT 3, '지원1팀', 2 FROM DUAL
UNION ALL SELECT 4, '지원2팀', 2 FROM DUAL
UNION ALL SELECT 5, '기획실', 1 FROM DUAL
UNION ALL SELECT 6, '기획1팀', 5 FROM DUAL
UNION ALL SELECT 7, '기획2팀', 5 FROM DUAL
), SAL AS (
SELECT 201801 AS yyyydd, 2 AS code, '홍길동' AS user_name, 100 AS sal FROM DUAL
UNION ALL SELECT 201801, 3, '이름2', 50 FROM DUAL
UNION ALL SELECT 201801, 4, '이름3', 80 FROM DUAL
UNION ALL SELECT 201801, 6, '이름4', 90 FROM DUAL
UNION ALL SELECT 201801, 6, '고길동', 1000 FROM DUAL
)
SELECT
LPAD(' ', (LEVEL * 2), ' ')||org_name,
user_name, sal
FROM
ORG
LEFT JOIN
SAL
ON
ORG.code = SAL.code
START WITH
ORG.code = 1
CONNECT BY
PRIOR ORG.code = ORG.parent_code;
완전 빠른데요. ㅎㅎㅎ
아발라네님 답변 감사합니다.
기획 2팀은 나오지 않아야 하거든요. ^^ 급여 테이블에 값이 없으니
그러나
급여테이블에 기획실은 기획1팀이 급여 테이블에 데이타가 있으니 상위 부서인 기획실이 조회 되야 하고요.
감사합니다.
급여가 있는 사원이 있는 부서가 출력이 되고,
급여가 있는 사원이 없는 부서는 출력이 되지 않도록 기존 SQL를 수정했습니다.
WITH ORG AS (
SELECT 1 AS code, '(주)ㅇㅇ회사' AS org_name, 0 parent_code FROM DUAL
UNION ALL SELECT 2, '지원실', 1 FROM DUAL
UNION ALL SELECT 3, '지원1팀', 2 FROM DUAL
UNION ALL SELECT 4, '지원2팀', 2 FROM DUAL
UNION ALL SELECT 5, '기획실', 1 FROM DUAL
UNION ALL SELECT 6, '기획1팀', 5 FROM DUAL
UNION ALL SELECT 7, '기획2팀', 5 FROM DUAL
), SAL AS (
SELECT 201801 AS yyyydd, 2 AS code, '홍길동' AS user_name, 100 AS sal FROM DUAL
UNION ALL SELECT 201801, 3, '이름2', 50 FROM DUAL
UNION ALL SELECT 201801, 4, '이름3', 80 FROM DUAL
UNION ALL SELECT 201801, 6, '이름4', 90 FROM DUAL
)
SELECT * FROM (
SELECT
LPAD(' ', (LEVEL * 2), ' ')||org_name,
user_name, sal,
DECODE(
ORG.code,
LEAD(ORG.parent_code) OVER(ORDER BY ROWNUM),
1,
0) AS exist_child
FROM
ORG
LEFT JOIN
SAL
ON
ORG.code = SAL.code
START WITH
ORG.code = 1
CONNECT BY
PRIOR ORG.code = ORG.parent_code
) MT
WHERE
exist_child = 1
OR user_name IS NOT NULL
;
부서에 사원이 여러명인 경우 -> 데이터 중복 발생 -> 그로 인한 성능 저하
하위부서는 있는데 하위부서에 인원이 없는데도 상위부서가 출력되는 오류
아.. 그렇군요. 지금 출장가는데, 이따 다시 고민해봐야겠습니다. 말씀 감사합니다.
WITH dept AS
(
SELECT 1 deptno, '(주)OOO회사' dname, 0 p_deptno FROM dual
UNION ALL SELECT 2, '지원실' , 1 FROM dual
UNION ALL SELECT 3, '지원1팀', 2 FROM dual
UNION ALL SELECT 4, '지원2팀', 2 FROM dual
UNION ALL SELECT 5, '기획실' , 1 FROM dual
UNION ALL SELECT 6, '기획1팀', 5 FROM dual
UNION ALL SELECT 7, '기획2팀', 5 FROM dual
)
, salary AS
(
SELECT '201801' ym, 2 deptno, '홍길동' ename, 100 sal FROM dual
UNION ALL SELECT '201801', 3, '이름2', 50 FROM dual
UNION ALL SELECT '201801', 4, '이름3', 80 FROM dual
UNION ALL SELECT '201801', 6, '이름4', 90 FROM dual
)
SELECT a.*
FROM dept a
, (SELECT DISTINCT deptno
FROM dept
START WITH deptno IN (SELECT deptno FROM salary WHERE ym = '201801')
CONNECT BY deptno = PRIOR p_deptno
) b
WHERE a.deptno = b.deptno
START WITH a.p_deptno = 0
CONNECT BY PRIOR a.deptno = a.p_deptno
;
마농님, 아발란체님 모두 감사합니다. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.