with 语句

with 语句优点：一次定义多次使用，不用重复解析，结果存放在临时表中.

 

The WITH query_name clause lets you assign a name to a subquery       block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle      optimizes the query by treating the query name as either an inline view or as a temporary table.
with 语句可以让我们给子查询语句块定义一个名字，然后可以使用这个名字代替该语句块在多处使用，oracle将with语句的结果当着内部视图或临时表

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is       visible to the main query and to all subsequent subqueries except the subquery that  defines the query name itself.
我们可以在最顶层的select声明中或子查询中定义with语句，并且其作用域是全局的（with内部不能用他自身）

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the       query such as where one averaged value needs to be compared against two or three times.
当一个子查询在多处被使用时最好使用with语句代替，例如：在多个地方要求平均数用于比较时。

 

WITH <alias_one> AS
   (subquery_sql_statement),
      <alias_two> AS
   (sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;

WITH qb1 AS
   (SELECT inst_id FROM gv$session),
       qb2 AS
   (SELECT unique inst_id FROM qb1
    UNION ALL
    SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;