데이터베이스 질문과 답변
집계함수에서 1월부터 12월 까지 표현하고 싶습니다.
0
3
2,378
by 겨울눈 [2013.11.05 15:02:28]
WITH T AS ( SELECT '1' AS COUNT1 ,'201303' AS YMD FROM DUAL UNION ALL SELECT '2' AS COUNT1 ,'201304' AS YMD FROM DUAL UNION ALL SELECT '3' AS COUNT1 ,'201305' AS YMD FROM DUAL UNION ALL SELECT '4' AS COUNT1 ,'201306' AS YMD FROM DUAL UNION ALL SELECT '5' AS COUNT1 ,'201207' AS YMD FROM DUAL ) SELECT SUM(COUNT1),YMD FROM T GROUP BY YMD ORDER BY YMD; SUM YMD 5 201207 1 201303 2 201304 3 201305 4 201306 이렇게 값이 나오는걸 null 201201 null 201202 null 201203 null 201204 null 201205 null 201206 5 201207 ....중략 null 201301 null 201302 1 201303 .... 이런식으로 표현하고 싶은데 어떤식으로 하면 될까요?
by 우리집아찌
[2013.11.05 15:29:49]
-- 좀 깔끔치 못하게 나왔습니다. WITH T AS ( SELECT '1' AS COUNT1 ,'201303' AS YMD FROM DUAL UNION ALL SELECT '2' AS COUNT1 ,'201304' AS YMD FROM DUAL UNION ALL SELECT '3' AS COUNT1 ,'201305' AS YMD FROM DUAL UNION ALL SELECT '4' AS COUNT1 ,'201306' AS YMD FROM DUAL UNION ALL SELECT '5' AS COUNT1 ,'201207' AS YMD FROM DUAL ) , T2 AS ( SELECT a.YMD || LPAD(b.lv,2,'0') YMD FROM (SELECT DISTINCT SUBSTR(YMD,1,4) YMD FROM T ) a , (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 12 ) b ) SELECT a.CNT , b.YMD FROM (SELECT SUM(COUNT1) CNT,YMD FROM T GROUP BY YMD ORDER BY YMD )a , T2 b WHERE b.YMD = a.YMD(+) ORDER BY b.YMD
by feelie
[2013.11.05 16:01:22]
10g 라면 파티션 아웃터조인을 사용하심이...
http://scidb.tistory.com/ -
New Features 의 중요성(Partition Outer Join 의 사용법)
by 겨울눈
[2013.11.05 16:01:48]
감사합니다 해결 했습니다
) 버튼을 클릭하여 작성 하시면 됩니다.