使用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_count
FROM 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) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND 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_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND 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;
结果: