데이터베이스 질문과 답변
현재 인사정보에 부서정보가 입력돼있는데요.
예를 들어 emp 테이블에
empNo div1 div2 div3
kim 1사업부 1부서 1팀
lee 1사업부 1부서 1팀
yun 1사업부 1부서 2팀
choi 1사업부 2부서 1팀
park 2사업부 1부서 1팀
위와 같이 데이터가 저장이되어있습니다.
위 정보를 가지고
조직 사용인원
1사업부 4
└1부서 3
└1팀 2
└ kim 1
└ lee 1
└2팀 1
└ yun 1
└2부서 1
└1팀 1
└ kim 1
위와 같은 정보를 얻고 싶습니다.
부서 구조를 계층형으로 바꿔서 해야할지 아니면 현재 테이블 구조만으로 위와 같은 쿼리를 뽑으수있는지
도저히 답이 안나와서 질문드립니다. 답변부탁드립니다.
by 마농
[2015.06.02 11:33:49]
WITH emp AS ( SELECT 'kim' empNo, '1사업부' div1, '1부서' div2, '1팀' div3 FROM dual UNION ALL SELECT 'lee' , '1사업부', '1부서', '1팀' FROM dual UNION ALL SELECT 'yun' , '1사업부', '1부서', '2팀' FROM dual UNION ALL SELECT 'choi', '1사업부', '2부서', '1팀' FROM dual UNION ALL SELECT 'park', '2사업부', '1부서', '1팀' FROM dual ) SELECT DECODE(1, GROUPING(div1), '전체' , GROUPING(div2), div1 , GROUPING(div3), ' └' || div2 , GROUPING(empNo), ' └' || div1 , ' └' || empNo ) div , COUNT(*) cnt -- , div1, div2, div3, empNo FROM emp GROUP BY ROLLUP(div1, div2, div3, empNo) ORDER BY div1 NULLS FIRST , div2 NULLS FIRST , div3 NULLS FIRST , empNo NULLS FIRST ;
by 손님
[2015.06.02 13:49:51]
감사합니다. 오라클 안되는게 없군요 ^^;
by 마농
[2015.06.02 14:38:11]
MSSQL 도 됩니다.
WITH emp AS ( SELECT 'kim' empNo, '1사업부' div1, '1부서' div2, '1팀' div3 UNION ALL SELECT 'lee' , '1사업부', '1부서', '1팀' UNION ALL SELECT 'yun' , '1사업부', '1부서', '2팀' UNION ALL SELECT 'choi', '1사업부', '2부서', '1팀' UNION ALL SELECT 'park', '2사업부', '1부서', '1팀' ) SELECT CASE 1 WHEN GROUPING(div1) THEN '전체' WHEN GROUPING(div2) THEN div1 WHEN GROUPING(div3) THEN ' └' + div2 WHEN GROUPING(empNo) THEN ' └' + div1 ELSE ' └' + empNo END div , COUNT(*) cnt -- , div1, div2, div3, empNo FROM emp GROUP BY div1, div2, div3, empNo WITH ROLLUP ORDER BY GROUPING(div1) DESC, div1 , GROUPING(div2) DESC, div2 , GROUPING(div3) DESC, div3 , GROUPING(empNo) DESC, empNo ;
by 창조의날개
[2015.06.02 15:47:54]
-- 부서를 계층형으로 만들어 보았어요..
WITH emp AS
(
SELECT 'kim' empNo, '1사업부' div1, '1부서' div2, '1팀' div3 FROM dual
UNION ALL SELECT 'lee' , '1사업부', '1부서', '1팀' FROM dual
UNION ALL SELECT 'yun' , '1사업부', '1부서', '2팀' FROM dual
UNION ALL SELECT 'choi', '1사업부', '2부서', '1팀' FROM dual
UNION ALL SELECT 'park', '2사업부', '1부서', '1팀' FROM dual
)
, T1 AS(
SELECT ROWNUM RN, EMP.* FROM EMP
)
, T2 AS(
SELECT EMPNO, (SELECT MIN(RN) FROM T1 WHERE EMPNO = AA.EMPNO) EMPNO_CD
, DIV1, (SELECT MIN(RN) FROM T1 WHERE DIV1 = AA.DIV1) DIV1_CD
, DIV2, (SELECT MIN(RN) FROM T1 WHERE DIV1 = AA.DIV1 AND DIV2 = AA.DIV2) DIV2_CD
, DIV3, (SELECT MIN(RN) FROM T1 WHERE DIV1 = AA.DIV1 AND DIV2 = AA.DIV2 AND DIV3 = AA.DIV3) DIV3_CD
FROM T1 AA
)
, T3 AS(
SELECT 0 UP_CD, DIV1_CD*1000000 CD, div1 NM FROM T2 GROUP BY DIV1_CD, div1 UNION ALL
SELECT DIV1_CD*1000000, DIV1_CD*1000000+DIV2_CD*10000 CD, div2 FROM T2 GROUP BY DIV1_CD,DIV2_CD, div2 UNION ALL
SELECT DIV1_CD*1000000+DIV2_CD*10000, DIV1_CD*1000000+DIV2_CD*10000+DIV3_CD*100 CD, div3 FROM T2 GROUP BY DIV1_CD,DIV2_CD,DIV3_CD, div3 UNION ALL
SELECT DIV1_CD*1000000+DIV2_CD*10000+DIV3_CD*100, DIV1_CD*1000000+DIV2_CD*10000+DIV3_CD*100+EMPNO_CD CD, EMPNO FROM T2
)
SELECT LPAD(' ', 4*(LEVEL-1)) || DECODE(LEVEL,1,'','└') ||NM AS NAME
, DECODE(LEVEL,1,(SELECT COUNT(*) FROM T3 WHERE MOD(CD,100)>0 AND FLOOR(CD/100000)=FLOOR(AA.CD/100000))
,2,(SELECT COUNT(*) FROM T3 WHERE MOD(CD,100)>0 AND FLOOR(CD/1000)=FLOOR(AA.CD/1000))
,3,(SELECT COUNT(*) FROM T3 WHERE MOD(CD,100)>0 AND FLOOR(CD/10)=FLOOR(AA.CD/10))
,1) CNT
FROM T3 AA
START WITH UP_CD=0
CONNECT BY PRIOR CD = UP_CD
;
) 버튼을 클릭하여 작성 하시면 됩니다.