데이터베이스 질문과 답변
SELECT
RANK_C 부서,
’총직원’ AS 구분,
COUNT(CASE WHEN REG_D=’01’ THEN 1 END) AS A사,
COUNT(CASE WHEN REG_D=’02’ THEN 1 END) AS B사,
COUNT(CASE WHEN REG_D=’03’ THEN 1 END) AS C사,
COUNT(CASE WHEN REG_D=’04’ THEN 1 END) AS D사,
COUNT(CASE WHEN REG_D=’05’ THEN 1 END) AS E사,
COUNT(CASE WHEN REG_D=’06’ THEN 1 END) AS F사
FROM TABLE
UNOIN
SELECT
RANK_C 부서,
’정규직’ AS 구분,
COUNT(CASE WHEN REG_D=’01’ THEN 1 END) AS A사,
COUNT(CASE WHEN REG_D=’02’ THEN 1 END) AS B사,
COUNT(CASE WHEN REG_D=’03’ THEN 1 END) AS C사,
COUNT(CASE WHEN REG_D=’04’ THEN 1 END) AS D사,
COUNT(CASE WHEN REG_D=’05’ THEN 1 END) AS E사,
COUNT(CASE WHEN REG_D=’06’ THEN 1 END) AS F사
FROM TABLE
WHERE DAE IS NOT NULL
| ROW | 부서 | 구분 | A사 | B사 | C사 | D사 | E사 | F사 | G사 |
| 1 | 기타 | 총직원 | 2 | 2 | 2 | 2 | 2 | 2 | 5 |
| 2 | 기타 | 정규직 | 1 | 1 | 1 | 1 | 1 | 1 | 3 |
| 3 | 기타 | 정규직원율 | 50% | 50% | 50% | 50% | 50% | 50% | 60% |
이렇게 있는데요 저기 ROW3의 퍼센트율을 구하고 싶은데요 어덯게 해야하나요.. 지금은 직원율이 안나오는상태입니다 ㅠㅠ
(참고로 아래 그림 처럼 로우가 꼭 2개가 아니라 부서가 여러개 있습니다. 실질적으로 원하는 화면은 아래입니다.)
저기 TABLE안에는 그룹바이로 한 또다른 서브쿼리입니다.
| ROW | 부서 | 구분 | A사 | B사 | C사 | D사 | E사 | F사 | G사 |
| 1 | 기타 | 총직원 | 2 | 2 | 2 | 2 | 2 | 2 | 5 |
| 2 | 기타 | 정규직 | 1 | 1 | 1 | 1 | 1 | 1 | 3 |
| 3 | 기타 | 정규직원율 | 50% | 50% | 50% | 50% | 50% | 50% | 60% |
| 4 | 관리부 | 총직원 | 2 | 2 | 2 | 2 | 2 | 2 | 5 |
| 5 | 관리부 | 정규직 | 1 | 1 | 1 | 1 | 1 | 1 | 3 |
| 6 | 관리부 | 정규직원율 | 50% | 50% | 50% | 50% | 50% | 50% | 60% |
| 7 | 경영부 | 총직원 | 2 | 2 | 2 | 2 | 2 | 2 | 5 |
| 8 | 경영부 | 정규직 | 1 | 1 | 1 | 1 | 1 | 1 | 3 |
| 9 | 경영부 | 정규직원율 | 50% | 50% | 50% | 50% | 50% | 50% | 60% |
SELECT '정규직' JIK, '관리부' DEPT, '01' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '01' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '02' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '03' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '05' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '06' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '01' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '관리부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '03' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '02' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '05' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '05' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '경영부' DEPT, '06' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '01' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '02' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '03' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '05' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '04' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '05' REG_D FROM DUAL UNION ALL
SELECT '정규직' JIK, '기타' DEPT, '06' REG_D FROM DUAL
)
SELECT DEPT
, JIK
-- , A_COMPANY , B_COMPANY , C_COMPANY,D_COMPANY, E_COMPANY, F_COMPANY
, DECODE(MOD(RN,3), 0, (LAG(A_COMPANY) OVER(ORDER BY DEPT, JIK))/A_COMPANY*100||'%', A_COMPANY) A_COM
, DECODE(MOD(RN,3), 0, (LAG(B_COMPANY) OVER(ORDER BY DEPT, JIK))/B_COMPANY*100||'%', B_COMPANY) B_COM
, DECODE(MOD(RN,3), 0, (LAG(C_COMPANY) OVER(ORDER BY DEPT, JIK))/C_COMPANY*100||'%', C_COMPANY) C_COM
, DECODE(MOD(RN,3), 0, (LAG(D_COMPANY) OVER(ORDER BY DEPT, JIK))/D_COMPANY*100||'%', D_COMPANY) D_COM
, DECODE(MOD(RN,3), 0, (LAG(E_COMPANY) OVER(ORDER BY DEPT, JIK))/E_COMPANY*100||'%', E_COMPANY) E_COM
, DECODE(MOD(RN,3), 0, (LAG(F_COMPANY) OVER(ORDER BY DEPT, JIK))/F_COMPANY*100||'%', F_COMPANY) F_COM
FROM (
SELECT DEPT, NVL(JIK,'총합계') JIK
, SUM(DECODE(REG_D,'01',1,0)) A_COMPANY
, SUM(DECODE(REG_D,'02',1,0)) B_COMPANY
, SUM(DECODE(REG_D,'03',1,0)) C_COMPANY
, SUM(DECODE(REG_D,'04',1,0)) D_COMPANY
, SUM(DECODE(REG_D,'05',1,0)) E_COMPANY
, SUM(DECODE(REG_D,'06',1,0)) F_COMPANY
, GROUPING_ID(DEPT, JIK) GID
, ROW_NUMBER() OVER(ORDER BY DEPT, JIK) RN
FROM T
GROUP BY GROUPING SETS((DEPT), ROLLUP(DEPT, JIK))
)
WHERE GID <= 1
) 버튼을 클릭하여 작성 하시면 됩니다.