오라클 기본편 3

1. ROLLUP() - 총합 또는 중간 합계가 필요할 때 사용

//이름과 id별로 합계와 총합을 조회

SELECT id, name SUM(a * b) AS "비용" FROM buyTBL

GROUP  BY ROLLUP  (name, id)

2.  GROUPING_ID() - 함수의 결과가 0이면 데이터, 1이면 합계를 위해 추가된 열

SELECT id, name SUM(a * b) AS "비용", GROUPING_ID(name) AS "추가행 여부" 

FROM buyTBL

GROUP  BY ROLLUP  (name, id)

3. WITH절 비재귀적 CTE 구조

WITH abc(id, total)

AS

( SELECT id, SUM(a * b) FROM buyTBL GROUP BY userID )

SELECT * FROM abc ORDER BY total DESC;

4. WITH절 재귀적 CTE 구조

WITH empCTE(empName, mgrName, dept, empLevel)

AS

(

    (SELECT  emp, manager, department, 0 FROM  empTBL WHERE manager = "없음")

    UNION ALL

    (SELECT empTbl.emp,  emmpTbl.manager, empTbl.department, empCTE.empLevenl + 1

      FROM empTbl INNER  JOIN empCTE ON  empTbl.manager = empCTE.empName)

)

SELECT * FROM empCTE ORDER BY dept, empLevel;