안녕하세요?

GROUP BY ROLLUP ()

으로 소계가 있는 쿼리를 만들다 막혀서 질문드립니다.

고마우신 분의 도움으로 아래 쿼리까지 진행되는 데 성공했는데요.

문제가 정렬이네요.

소계가 구해진 이후에 ORDER BY를 하면..

소계가 있는 ROW의 COLUMN 값이 빈값이라...

소계 ROW가 이상한 곳으로 가버려요.. ㅠㅠ

그렇다고 정렬을 안할 수 없고.

WITH 안에 해당하는 VIEW에 ORDER BY를 줘도 그룹핑후 정렬은 다 다시 되고...;;;


아래 예시 쿼리 붙입니다.
CODE1, CODE2, CODE3 (CASE CODE3_1, CODE3_2) 로 롤업하는 쿼리입니다.
제가 원하는건 아래 자료가 CODE1~3으로 정렬되고 YEAR와 MONTH로도 정렬이 되었으면 합니다.

예에서 영역 6의 생물과 스판.. ROW 보시면 2009년 자료인 스판이 아래에 있는데요.. 위로 올라왔으면 합니다.



그럼 도움 바랍니다. 이틀 째 진도를 못 빼고 있으니 눈치만 보이네요. ㅠㅠ 흑흑..

 

WITH
T1 AS (
SELECT '00' CODE1,'01' CODE2,'01' CODE3_1_CD,'영역1' CODE3_1_NM,'103' CODE3_2,'시민' NAME ,'3' POINT ,'2011' YEAR, '01' MONTH, 'ASD' ETC FROM DUAL UNION ALL
SELECT '00','01','02','영역1','104','신석'  ,'1','2009','12','SD' FROM DUAL UNION ALL
SELECT '11','08','' ,''  ,'' ,'일어'  ,'3','2009','08','12' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','음악'  ,'3','2008','08','2' FROM DUAL UNION ALL
SELECT '00','02','11','영역4','101','심사'  ,'3','2009','08','ADS' FROM DUAL UNION ALL
SELECT '00','01','04','영역2','101','철도'  ,'3','2010','05','1' FROM DUAL UNION ALL
SELECT '00','01','05','영역3','102','G드래곤' ,'3','2009','06','R3' FROM DUAL UNION ALL
SELECT '00','02','21','영역7','106','신사'  ,'1','2008','03','2' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','골프'  ,'1','2010','12','13' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','대학'  ,'3','2010','11','AS' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','공사'  ,'3','2008','01','D' FROM DUAL UNION ALL
SELECT '00','01','06','영역3','102','영어'  ,'3','2011','02','A' FROM DUAL UNION ALL
SELECT '00','02','22','영역7','106','실사'  ,'1','2008','04','C' FROM DUAL UNION ALL
SELECT '00','02','23','영역7','106','중국'  ,'1','2010','05','S' FROM DUAL UNION ALL
SELECT '00','02','24','영역7','106','이랜드' ,'1','2008','07','C' FROM DUAL UNION ALL
SELECT '00','02','12','영역5','102','법'  ,'3','2010','08','C' FROM DUAL UNION ALL
SELECT '00','02','14','영역6','104','스판'  ,'3','2009','09','C' FROM DUAL UNION ALL
SELECT '00','02','14','영역6','104','생물'  ,'3','2010','05','W' FROM DUAL UNION ALL
SELECT '00','01','03','영역2','101','철기시대' ,'3','2008','05','DS' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','스키'  ,'1','2010','05','W' FROM DUAL UNION ALL
SELECT '11','08','' ,''  ,'' ,'예술'  ,'3','2008','05','Q' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','승부'  ,'1','2009','05','3' FROM DUAL UNION ALL
SELECT '00','02','26','영역8','105','수나라' ,'1','2010','05','2' FROM DUAL UNION ALL
SELECT '11','08','' ,''  ,'' ,'어린이' ,'3','2011','05','2' FROM DUAL 
)
SELECT 
 CODE1,
 CODE2,
 CASE WHEN YEAR >= '2008' AND CODE1 = '00' THEN CODE3_1_NM ELSE CODE3_2 END AS CODE3,
 NAME,
 SUM(POINT),
 YEAR,
 MONTH,
 CODE3_1_CD,
 CODE3_2,
 ETC
FROM T1
GROUP BY ROLLUP(CODE1, CODE2, CASE WHEN YEAR >= '2008' AND CODE1 = '00' THEN CODE3_1_NM ELSE CODE3_2 END, (NAME,YEAR,MONTH,CODE3_1_CD,CODE3_1_NM,CODE3_2,YEAR,ETC))
ORDER BY CODE1, CODE2

에고..그럼 부탁드려요..

감사합니다.

질문이 이상하거나. 미진하면 말씀해주세요..

수정하거나 보충하겠습니다.