子查询定义从句总结（WITH AS 语句）

原文传送门：http://www.itpub.net/thread-1235717-1-3.html

 

“子查询定义”从句总结（WITH AS 语句）

 

“子查询定义”从句总结（WITH AS 语句）

 

一、先看下oracle 10g SQL参考文档(Ora 10G SQL Reference)中的描述(以下汉译部分，仅是个人理解，仅供参考)：
1 、 “子查询定义”从句 的 定义格式 :
WITH query_name AS (subquery)      [, query_name AS (subquery) ]...     2 、 subquery_factoring_clause ( “子查询定义” 从句部分 )

 

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 Database optimizes the query by treating the query name as either an inline view or as a temporary table.

 

 

(使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。)

 

 

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语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用 ）

 

 

3、Restrictions on Subquery Factoring (“子查询定义”从句的限制性)

 

 

·         You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.

 

 

（不能嵌套定义，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。）

 

 

·         In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.

 

 

(在带集合操作的查询语句中，除FROM子查询外,子查询中不能出现“子查询定义”语句。但通过测试后感觉并不是这样，详见后面的示例。不知道是不是对此段英文理解错误。)

 

 

二、        个人总结及示例:

 

 

1、浅谈该功能的oracle 原理：先对子查询起个名称，然后可以多处引用，并像内联视图或临时表一样对待，从而减少访问基表的次数，提高运行效率。

 

 

2、使用时机，一般在需要多次访问一个子查询结果集的情况下使用。注意oracle数据库版本的限制，该语句是在9i及以后数据库版本中才出现的。

 

 

3、使用原则及相关示例：

 

1>、 定义之后一定要引用
SQL> select * from  (
   2 WITH t_with_3 as (SELECT '1' a ,'3' b FROM DUAL)
  3  SELECT '1' a ,'1' b FROM dual -- 此处未引用
  4  )
  5  /
WITH t_with_3 as (SELECT '1' a ,'3' b FROM DUAL)
                                           *
ERROR 位于第 2 行：
ORA-32035: unreferenced query name defined in WITH clause

2>、同一个主查询语句的同级别的地方,子查询定义语句只能出现一个,而不能并列出现多个
例1：
SQL>  SELECT  *
  2    FROM (
  3          WITH
  4            t_with_1 AS (SELECT '1' a ,'1' b FROM DUAL),
  5          WITH   -- 此处再次出现定义
  6            t_with_2 AS (SELECT '1' a ,'2' b FROM DUAL)
  7*         SELECT x.a , x.b xb ,y.b yb FROM t_with_1 x , t_with_2 y
  8  /
        WITH
        *
ERROR 位于第 5 行：
ORA-00903: invalid table name

例2：带集合操作的查询

SQL>  SELECT  *
  2    FROM (
  3          WITH
  4            t_with_1 AS (SELECT '1' a ,'1' b FROM DUAL)
  5          -- 此后的整个语句被认为是一个主查询语句
  6          SELECT x.a , x.b FROM t_with_1 x
  7          union all
  8          WITH   -- 此处再次出现定义
  9            t_with_2 AS (SELECT '1' a ,'2' b FROM DUAL)
10*         SELECT y.a ,y.b FROM t_with_2 y
11  /
        SELECT y.a ,y.b FROM t_with_2 y
        *
ERROR 位于第 10 行：
ORA-32034: unsupported use of WITH clause

3>、不能嵌套定义
SQL>  SELECT  *
  2    FROM (
  3          WITH
  4            t_with_1 AS (  select * from (
  5                                          WITH t_with_x as (SELECT '1' a ,'x' b FROM DUAL)
  6                                          SELECT '1' a ,'1' b FROM t_with_x
  7                                          )
  8                         ),
  9            t_with_2 AS (SELECT '1' a ,'2' b FROM DUAL)
10*         SELECT x.a , x.b xb ,y.b yb FROM t_with_1 x , t_with_2 y
11  /
                                        WITH t_with_x as (SELECT '1' a ,'x' b FROM DUAL)
                                        *
ERROR 位于第5行：
ORA-32034: unsupported use of WITH clause

4>、可"嵌套"引用(未定义之前不能引用)
SQL>  SELECT  *
  2    FROM (
  3          WITH
  4            t_with_1 AS (SELECT '1' a ,'1' b FROM DUAL),
  5            t_with_2 AS (SELECT '1' a ,'2' b FROM DUAL),
  6            t_with_3 as (SELECT *  FROM t_with_1)
  7          SELECT x.a , x.b xb ,y.b yb,z.b zb FROM t_with_1 x , t_with_2 y ,t_with_3 z
  8*         )
  9  /

A X Y Z
- - - -
1 1 2 1

5>、可在大多数类型的子查询中引用
SQL>  SELECT a ,b
  2    FROM (
3          WITH
  4            t_with AS (SELECT '1' a FROM DUAL)
  5          SELECT x.a ,(select a from t_with) b
  6            FROM t_with x,
  7                 (select a from t_with) y
  8           WHERE x.a= y.a
  9              and x.a = (select a from t_with)
10*         )
11  /

A B
- -
1 1

6>、可在大多数类型的子查询中定义及引用
例1：
SQL>  SELECT a ,b
  2    FROM (
  3          WITH
  4            t_with AS (SELECT '1' a FROM DUAL)
  5          SELECT x.a ,(
  6                        WITH t_with_z as (SELECT '1' a FROM t_with )
  7                        SELECT  s_1.a FROM t_with_z s_1 ,t_with s_2
  8                       ) b
  9            FROM t_with x,
10                 (  WITH t_with_y as (SELECT '1' a FROM t_with)
11                    SELECT  f_1.a FROM t_with_y f_1, t_with f_2
12                 ) y
13           WHERE x.a= y.a
14              and x.a = ( WITH t_with_x as (SELECT '1' a FROM t_with)
15                          SELECT  w_1.a FROM t_with_x w_1 ,t_with w_2
16                        )
17*        )
18  /

A B
- -
1 1

例2：带集合操作的查询（ 注意并非FROM子查询中才能使用“子查询定义”从句）
SQL>  SELECT  *
  2    FROM (
  3          WITH
  4              t_with_1 AS (SELECT '1' a ,'1' b FROM DUAL)
  5          SELECT x.a , x.b ,'x' c FROM t_with_1 x
  6          union all
  7          SELECT y.a ,y.b ,  (WITH
  8                                  t_with_x AS (SELECT * FROM t_with_1)
  9                                  SELECT a FROM t_with_x )
10            FROM (  WITH
11                       t_with_2 AS (SELECT '1' a ,'2' b FROM DUAL)
12                    SELECT a ,b FROM t_with_2
13                                ) y
14          WHERE  y.a = (  WITH
15                              t_with_3 AS (SELECT '1' a ,'2' b FROM DUAL)
16                              SELECT a FROM t_with_3
17                       )
18*         )
19  /

A B C
- - -
1 1 x
1 2 1