关于oracle with table as 创建临时表的用法示例

1、with table as 相当于建个临时表（用于一个语句中某些中间结果放在临时表空间的SQL语句），Oracle 9i 新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。

语法就是
with tempname as (select ....)
select ...

例子：
with t as (select * from emp where depno=10)
select * from t where empno=xxx

with
wd as (select did,arg(salary) 平均工资 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;



2、何时被清除
临时表不都是会话结束就自动被PGA清除嘛! 但with as临时表是查询完成后就被清除了！
23:48:58 SCOTT@orcl> with aa as(select * from dept)
23:57:58   2  select * from aa;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

已用时间:  00: 00: 00.12
23:58:06 SCOTT@orcl> select * from aa;
select * from aa
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


已用时间:  00: 00: 00.02
23:58:14 SCOTT@orcl>

3、就这一功能来说，子查询就可以达到啊，为什么要用with呢？ 用with有什么好处？
都能写，但执行计划不同的。当有多个相似子查询的时候，用with写公共部分，因为子查询结果在内存临时表中，执行效率当然就高啦~

4、问题：
有张表数据如下：
aaa 高
bbb 低
aaa 低
aaa 高
bbb 低
bbb 高
需要得到下列结果,
  高 低
aaa 2 1
bbb 1 2
问 SQL 语句怎么写？？

答案：
with tt as (
  select 'aaa' id, '高' value from dual union all
  select 'bbb' id, '低' value from dual union all
  select 'aaa' id, '低' value from dual union all
  select 'aaa' id, '高' value from dual union all
  select 'bbb' id, '低' value from dual union all
  select 'bbb' id, '高' value from dual)
SELECT id,
       COUNT(decode(VALUE, '高', 1)) 高,
       COUNT(decode(VALUE, '低', 1)) 低
  FROM tt
 GROUP BY id;
===================================================================
扩展：
Oracle9i新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。

　　一个简单的例子:

SQL> WITH
2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
5 FROM OBJ O, SEG S
6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)
7 ;
OBJECT_NAME OBJECT_TYPE SIZE_K
------------------------------ ------------------- ----------
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128

　　通过WITH语句定义了两个子查询SEG和OBJ，在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出，使用WITH语句，将一个包含聚集、外连接等操作SQL清晰的展现出来。

　　WITH定义的子查询不仅可以使查询语句更加简单、清晰，而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

　　即使是在WITH的定义层中，后定义的子查询都可以使用前面已经定义好的子查询:

SQL> WITH
2 Q1 AS (SELECT 3 + 5 S FROM DUAL),
3 Q2 AS (SELECT 3 * 5 M FROM DUAL),
4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
5 SELECT * FROM Q3;
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120

　　利用WITH定义查询中出现多次的子查询还能带来性能提示。Oracle会对WITH进行性能优化，当需要多次访问WITH定义的子查询时，Oracle会将子查询的结果放到一个临时表中，避免同样的子查询多次执行，从而有效的减少了查询的IO数量。

WITH能用在SELECT语句中，UPDATE和DELETE语句也是支持WITH语法的，只是需要版本支持:
http://www.oracle.com.cn/viewthread.php?tid=83530

=============================================================================
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);

再举个简单的例子

with a as (select * from test)

select * from a;

其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了


这是搜索到的英文文档资料(说得比较全,但是本人英文特菜,还没具体了解到,希望各高手具体谈谈这个with
as 的好处)

About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

   ? The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
   ? Formally, the “WITH clause” is called subquery factoring
   ? The SQL “WITH clause” is used when a subquery is executed multiple times
   ? Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH  clause”:

WITH
sum_sales AS
  select /*+ materialize */
    sum(quantity) all_sales from stores
number_stores AS
  select /*+ materialize */
    count(*) nbr_stores from stores
sales_by_store AS
  select /*+ materialize */
  store_name, sum(quantity) store_sales from
  store natural join sales
SELECT
   store_name
FROM
   store,
   sum_sales,
   number_stores,
   sales_by_store
where
   store_sales > (all_sales / nbr_stores)
;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”: