데이터베이스 질문과 답변
테이블
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 WORK_DATE
==== ======= ======== =====
1 1 1 1 1 1 1 1 04
10 10 10 10 10 10 10 10 03
1 1 1 1 1 1 1 1 03
4 4 4 4 4 4 54 4 04
1 1 1 1 1 1 1 1 03
55 50 55 0 160 0 0 0 03
1 1 1 2 5 0 0 0 03
1 1 1 1 4 1 1 1 03
1 1 1 1 4 4 4 4 02
1 2 3 55 61 3 3 3 02
3 3 3 3 2 2 2 2 05
10 1 1 1 2 1 10 1 03
정규직은 COL1 합
비정규직은 COL1~COL7의합
일반직은 COL8 의합을 달별기준으로 뿌려줄려고합니다 WORK_DATE 달별 기준으로
NAME CNT1,CNT2,CNT3,CNT4,CNT5,CNT6,CNT7~ CNT12
===== ==== ==== ==== ==== ==== ==== ~12
1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
정규직 1 2 3 4 5 6 7 8 9 10 11 12
비정규직 1 2 3 4 5 6 7 8 9 10 11 12
일반 1 2 3 4 5 6 7 8 9 10 11 12
총인원 3 6 9 12 15 18 21 24 ~
쿼리짜기 어려움있어서 도움을 요청드립니다. ㅠㅠ
예시가 빈약하네요. DBMS 종류도 없고.
work_date 가 두자리인게 이상하고,
일반직인지 정규직인지 구분 항목도 안보이네요.
-- Oracle --
WITH t AS
(
SELECT 1 c1, 1 c2, 1 c3, 1 c4, 1 c5, 1 c6, 1 c7, 1 c8, '201904' dt, '일반' gb FROM dual
UNION ALL SELECT 10, 10, 10, 10, 10, 10, 10, 10, '201903', '정규직' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, '201903', '정규직' FROM dual
UNION ALL SELECT 4, 4, 4, 4, 4, 4, 54, 4, '201904', '정규직' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, '201903', '정규직' FROM dual
UNION ALL SELECT 55, 50, 55, 0, 160, 0, 0, 0, '201903', '정규직' FROM dual
UNION ALL SELECT 1, 1, 1, 2, 5, 0, 0, 0, '201903', '비정규직' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 4, 1, 1, 1, '201903', '비정규직' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 4, 4, 4, 4, '201902', '비정규직' FROM dual
UNION ALL SELECT 1, 2, 3, 55, 61, 3, 3, 3, '201902', '비정규직' FROM dual
UNION ALL SELECT 3, 3, 3, 3, 2, 2, 2, 2, '201905', '비정규직' FROM dual
UNION ALL SELECT 10, 1, 1, 1, 2, 1, 10, 1, '201903', '비정규직' FROM dual
)
SELECT gb
, SUM(DECODE(mm, '01', c)) m01
, SUM(DECODE(mm, '02', c)) m02
, SUM(DECODE(mm, '03', c)) m03
, SUM(DECODE(mm, '04', c)) m04
, SUM(DECODE(mm, '05', c)) m05
, SUM(DECODE(mm, '06', c)) m06
, SUM(DECODE(mm, '07', c)) m07
, SUM(DECODE(mm, '08', c)) m08
, SUM(DECODE(mm, '09', c)) m09
, SUM(DECODE(mm, '10', c)) m10
, SUM(DECODE(mm, '11', c)) m11
, SUM(DECODE(mm, '12', c)) m12
FROM (SELECT gb
, SUBSTR(dt, 5, 2) mm
, DECODE(gb
, '정규직' , c1
, '비정규직', c1+c2+c3+c4+c5+c6+c7
, '일반' , c8
) c
FROM t
)
GROUP BY ROLLUP(gb)
;
답변감사드리고 죄송합니다 종류는 오라클입니다
제가한번 어제 쿼리 짜봣는데 문제될게 있을까요? 그룹바이를 2번감싸서 문제될꺼같은데
SELECT DIV cntDiv, SUM(cnt1) cnt1, SUM(cnt2) cnt2, SUM(cnt3) cnt3, SUM(cnt4) cnt4, SUM(cnt5) cnt5, SUM(cnt6) cnt6, SUM(cnt7) cnt7, SUM(cnt8) cnt8, SUM(cnt9) cnt9, SUM(cnt10) cnt10, SUM(cnt11) cnt11, SUM(cnt12) cnt12 FROM( SELECT DIV, DECODE(work_date,'01',cnt,0) cnt1, DECODE(work_date,'02',cnt,0) cnt2, DECODE(work_date,'03',cnt,0) cnt3, DECODE(work_date,'04',cnt,0) cnt4, DECODE(work_date,'05',cnt,0) cnt5, DECODE(work_date,'06',cnt,0) cnt6, DECODE(work_date,'07',cnt,0) cnt7, DECODE(work_date,'08',cnt,0) cnt8, DECODE(work_date,'09',cnt,0) cnt9, DECODE(work_date,'10',cnt,0) cnt10, DECODE(work_date,'11',cnt,0) cnt11, DECODE(work_date,'12',cnt,0) cnt12 FROM( SELECT DIV, WORK_DATE, SUM(cnt) cnt FROM( SELECT DIV, sum(cnt) cnt , WORK_DATE FROM ( SELECT '정규직' div, TO_NUMBER(NVL(col1,0)) cnt , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' UNION ALL SELECT '비정규직' div, TO_NUMBER(NVL(col2+col3+col4+col5+col6+col7 ,0)) cnt , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' UNION ALL SELECT '일반직' div, TO_NUMBER(NVL(col8,0)) , TO_CHAR(WORK_DATE , 'MM') work_date FROM DUAL WHERE USE_YN ='Y' ) GROUP BY DIV, WORK_DATE )A GROUP BY DIV, WORK_DATE )B )C GROUP BY ROLLUP(DIV) ORDER BY DIV
쿼리가 너무 복잡합니다.
dual 이라고 표현한 부분 정확하게 표현해 주세요.
dual 로 표현한 부분을 동일 테이블이라고 간주하고 답변 드립니다.
WITH t AS
(
SELECT 1 col1, 1 col2, 1 col3, 1 col4, 1 col5, 1 col6, 1 col7, 1 col8, TO_DATE('201904', 'yyyymm') work_date, 'Y' use_yn FROM dual
UNION ALL SELECT 10, 10, 10, 10, 10, 10, 10, 10, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 4, 4, 4, 4, 4, 4, 54, 4, TO_DATE('201904', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 55, 50, 55, 0, 160, 0, 0, 0, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 1, 1, 2, 5, 0, 0, 0, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 4, 1, 1, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 1, 1, 1, 4, 4, 4, 4, TO_DATE('201902', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 1, 2, 3, 55, 61, 3, 3, 3, TO_DATE('201902', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 3, 3, 3, 3, 2, 2, 2, 2, TO_DATE('201905', 'yyyymm'), 'Y' FROM dual
UNION ALL SELECT 10, 1, 1, 1, 2, 1, 10, 1, TO_DATE('201903', 'yyyymm'), 'Y' FROM dual
)
SELECT DECODE(lv, 1, '정규직', 2, '비정규직', 3, '일반', '합계') div
, NVL(SUM(DECODE(mm, '01', c)), 0) m01
, NVL(SUM(DECODE(mm, '02', c)), 0) m02
, NVL(SUM(DECODE(mm, '03', c)), 0) m03
, NVL(SUM(DECODE(mm, '04', c)), 0) m04
, NVL(SUM(DECODE(mm, '05', c)), 0) m05
, NVL(SUM(DECODE(mm, '06', c)), 0) m06
, NVL(SUM(DECODE(mm, '07', c)), 0) m07
, NVL(SUM(DECODE(mm, '08', c)), 0) m08
, NVL(SUM(DECODE(mm, '09', c)), 0) m09
, NVL(SUM(DECODE(mm, '10', c)), 0) m10
, NVL(SUM(DECODE(mm, '11', c)), 0) m11
, NVL(SUM(DECODE(mm, '12', c)), 0) m12
, SUM(c) tot
FROM (SELECT TO_CHAR(work_date , 'mm') mm
, lv
, DECODE(lv, 1, col1
, 2, col2+col3+col4+col5+col6+col7
, 3, col8
) c
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
WHERE use_yn = 'Y'
)
GROUP BY ROLLUP(lv)
;
제가 초반 질문글을 잘못올렸네요
저데이터는 예시 데이터라고 했어야했는데 죄송합니다.
저상태에서 유니온 만 지우면되겠네요
답변감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.