oracle中with as子句相当于把一个子查询的语句块定义一个别名(临时表),在之后紧跟的select查询中可以重复使用。
格式:
WITH <alias_name> AS
(
subquery_sql_statement
)
SELECT <column_name_list> FROM <alias_name>;
要注意的是在with as子句后面只能跟一个select查询语句,with生成的临时表在select结束后就会被释放掉。
使用with的好处是使代码的结构更清晰。在多次对一个子查询使用时,效率更高。
例子:
–查询出部门的总工资
WITH DEPT_COSTS AS
(SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME)
select * from DEPT_COSTS;
参考资料:
http://blog.itpub.net/11759632/viewspace-1060884/
我的oracle怎么不支持with as?
SQL> select max(score) from a_test_t1;
MAX(SCORE)
————————————————–
99
SQL> WITH MAX_SCORE AS (select max(score) from a_test_t1);
WITH MAX_SCORE AS (select max(score) from a_test_t1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
格式不对啊