LanguageSQL(Oracle)

5. ROLLUP, CUBE

ROWNUM

SELECT ROWNUM, EMPLOYEE_ID
FROM EMPLOYEES;

트리 형태 구조를 추출하기 위한 질의

– START WITH ~ CONNECT BY 절
SELECT LEVEL MANAGER_ID, EMPLOYEE_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER BY LEVEL, MANAGER_ID, EMPLOYEE_ID;

ROLLUP

부서별 직책별 평균월급, 사원수 평균월급을 출력하시오
ROLLUP (A, B): group by (A, B) & group by (A) & ALL

SELECT CASE GROUPING(DEPARTMENT_ID)
WHEN 1 THEN ‘모든 부서’
ELSE NVL(TO_CHAR(DEPARTMENT_ID),’없음’)
END AS “부서”,

   CASE GROUPING(JOB_ID)
        WHEN 1 THEN '모든 직업'
        ELSE NVL(TO_CHAR(JOB_ID),'없음')
   END AS "직업"

   ,COUNT(*),ROUND(AVG(SALARY),0)

FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID

GROUPING

SELECT CASE GROUPING(DEPARTMENT_ID)
WHEN 1 THEN ‘모든 부서’
ELSE NVL(TO_CHAR(DEPARTMENT_ID),’없음’)
END AS “부서”,

   CASE GROUPING(JOB_ID)
        WHEN 1 THEN '모든 직업'
        ELSE NVL(TO_CHAR(JOB_ID),'없음')
   END AS "직업"

   ,COUNT(*),ROUND(AVG(SALARY),0)

FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID

CUBE

group by (A,B) & group by (A) & group by (B) & ALL
SELECT CASE GROUPING(DEPARTMENT_ID)
WHEN 1 THEN ‘모든 부서’
ELSE NVL(TO_CHAR(DEPARTMENT_ID),’없음’)
END AS “부서”,

   CASE GROUPING(JOB_ID)
        WHEN 1 THEN '모든 직업'
        ELSE NVL(TO_CHAR(JOB_ID),'없음')
   END AS "직업"

   ,COUNT(*),ROUND(AVG(SALARY),0)

FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID

Share