oracle 子查询因子化 with as

使用SCOTT/TIGER登录。

SQL：

WITH TB_DEPT_COUNT AS (SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO) SELECT * FROM TB_DEPT_COUNT;

结果：

查看所有的表：

表TB_DEPT_COUNT，相当于临时表。

使用with as能简化SQL。

不使用WITH AS：

SELECT e.ename AS employee_name,       dc1.dept_count AS emp_dept_count,       m.ename AS manager_name,       dc2.dept_count AS mgr_dept_countFROM   emp e,       (SELECT deptno, COUNT(*) AS dept_count        FROM   emp        GROUP BY deptno) dc1,       emp m,       (SELECT deptno, COUNT(*) AS dept_count        FROM   emp        GROUP BY deptno) dc2WHERE  e.deptno = dc1.deptnoAND    e.mgr = m.empnoAND    m.deptno = dc2.deptno;

上面的子查询都是：

SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno

使用WITH AS：

WITH dept_count AS (  SELECT deptno, COUNT(*) AS dept_count  FROM   emp  GROUP BY deptno)SELECT e.ename AS employee_name,       dc1.dept_count AS emp_dept_count,       m.ename AS manager_name,       dc2.dept_count AS mgr_dept_countFROM   emp e,       dept_count dc1,       emp m,       dept_count dc2WHERE  e.deptno = dc1.deptnoAND    e.mgr = m.empnoAND    m.deptno = dc2.deptno;

就可以简化SQL了。

创建表：

CREATE TABLE EMP1 AS WITH TEMP_EMP AS (SELECT ROWNUM RN, E.* FROM EMP E) SELECT * FROM TEMP_EMP WHERE RN BETWEEN 4 AND 10;

结果：