데이터베이스 질문과 답변
by IT노동자 [2014.07.24 11:53:01]
GROUP | DATE | SPED
0 | 20140723010000 | 10
0 | 20140723020000 | 20
0 | 20140723030000 | 30
0 | 20140723040000 | 40
0 | 20140723050000 | 50
1 | 20140723010000 | 50
1 | 20140723020000 | 40
1 | 20140723030000 | 30
1 | 20140723040000 | 20
1 | 20140723050000 | 10
결과
그룹 01 | 02 | 03 | 04 | 05 | 06
0 10 20 30 40 50 0
1 50 40 30 20 10 0
마농님 알려주신 쿼리는 잘 분석해서 이용해보았는데요
만약에 저런 그룹으로 나누어 분석할려면 어떻게 해야되나요?
WITH t AS ( SELECT '20140723010000' dt, 10 sped FROM dual UNION ALL SELECT '20140723020000', 20 FROM dual UNION ALL SELECT '20140723030000', 30 FROM dual UNION ALL SELECT '20140723040000', 40 FROM dual UNION ALL SELECT '20140723050000', 50 FROM dual ) SELECT 'SPED' gb , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '01', sped)), 0) "01" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '02', sped)), 0) "02" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '03', sped)), 0) "03" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '04', sped)), 0) "04" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '05', sped)), 0) "05" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '06', sped)), 0) "06" FROM t WHERE dt LIKE '20140723%' ;
기존에 알려주셨던 쿼리문입니다
GROUP BY grp 만 추가하면 되겠네요.....................
감사합니다 이제야 이해했네요
WITH t AS ( SELECT 0 grp, '20140723010000' dt, 10 sped FROM dual UNION ALL SELECT 0, '20140723020000', 20 FROM dual UNION ALL SELECT 0, '20140723030000', 30 FROM dual UNION ALL SELECT 0, '20140723040000', 40 FROM dual UNION ALL SELECT 0, '20140723050000', 50 FROM dual UNION ALL SELECT 1, '20140723010000', 50 FROM dual UNION ALL SELECT 1, '20140723020000', 40 FROM dual UNION ALL SELECT 1, '20140723030000', 30 FROM dual UNION ALL SELECT 1, '20140723040000', 20 FROM dual UNION ALL SELECT 1, '20140723050000', 10 FROM dual ) SELECT 'SPED' gb , grp , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '01', sped)), 0) "01" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '02', sped)), 0) "02" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '03', sped)), 0) "03" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '04', sped)), 0) "04" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '05', sped)), 0) "05" , NVL(MIN(DECODE(SUBSTR(dt, 9, 2), '06', sped)), 0) "06" FROM t WHERE dt LIKE '20140723%' GROUP BY grp ORDER BY grp ;
감사합니다. 이제야 깨달았습니다. ㅜㅜ
좋은하루 보내세요^^
) 버튼을 클릭하여 작성 하시면 됩니다.