|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
/** SUB QUERY를 이용한 방법 **/ SELECT T1.EMPNO , T1.ENAME , T2.DNAME , T2.DCNT FROM SCOTT.EMP T1 LEFT OUTER JOIN ( SELECT A.DEPTNO , MAX(A.DNAME) AS DNAME , COUNT(B.EMPNO) AS DCNT FROM SCOTT.DEPT A LEFT OUTER JOIN SCOTT.EMP B ON B.DEPTNO = A.DEPTNO WHERE 1=1 GROUP BY A.DEPTNO ) T2 ON T2.DEPTNO = T1.DEPTNO WHERE 1=1 ;
/** WITH문을 이용한 방법 **/ WITH DEPT_CNT AS ( SELECT A.DEPTNO , MAX(A.DNAME) AS DNAME , COUNT(B.EMPNO) AS DCNT FROM SCOTT.DEPT A LEFT OUTER JOIN SCOTT.EMP B ON B.DEPTNO = A.DEPTNO WHERE 1=1 GROUP BY A.DEPTNO ) SELECT T1.EMPNO , T1.ENAME , T2.DNAME , T2.DCNT FROM SCOTT.EMP T1 LEFT OUTER JOIN DEPT_CNT T2 ON T2.DEPTNO = T1.DEPTNO WHERE 1=1 ;
|
cs |
테스트 코드
test
반응형
반응형