[SQLP실기풀이]6장 고급SQL튜닝(6) 고급 SQL 활용 55번

1) 변경한 쿼리

변경 전 쿼리

SQL> SELECT DEPTNO 부서번호
2 (CASE WHEN GROUPING(EMPNO) = 1 THEN '부서계' ELSE TO_CHAR(EMPNO) END) 사원번호
3 , SUM(SAL) 급여합, ROUND(AVG(SAL)) 급여평균
4 FROM EMP
5 GROUP BY DEPTNO, ROLLUP(EMPNO)
6 ORDER BY 1, 2;

변경 후 쿼리

SELECT DEPTNO , DECODE(LVL, 1, '부서계' ,2 ,EMPNO) AS 사원번호
, SUM(SAL) 급여합 , ROUND(AVG(SAL)) 급여평균
FROM EMP , (
	SELECT LEVEL AS LVL
  FROM DUAL
  WHERE CONNECT BY LEVEL <=2
	) T1
GROUP BY DEPNO , LVL , DECODE(LVL, 2, EMPNO)
ORDER BY 1, 2;

2) 기타

[쿼리 변환 중간 디버깅 산출물]

SELECT SUM(SAL) 급여합 , ROUND(AVG(SAL)) 급여평균
FROM EMP
GROUP BY DEPTNO;

SELECT SUM(SAL) 급여합 , ROUND(AVG(SAL)) 급여평균
FROM EMP
GROUP BY DEPTNO , EMPNO
ORDER BY 1,2;

SELECT LEVEL AS LVL
FROM DUAL
WHERE CONNECT BY LEVEL <=2;