with as

今天在网上查找优化的案例，碰巧在一个大神的博客里看到这个用法了，起初还很疑惑，所以就跟着做了下实验：

SQL> create table t2(id int); 

Table created.

SQL> create table t1(id int); 

Table created.

SQL> insert into t2 values(1);

1 row created.

SQL> insert into t2 values(2);

1 row created.

SQL> insert into t2 values(3);

1 row created.

SQL> insert into t1 values(1);

1 row created.

SQL> select * from t1;

        ID

----------

         1

SQL> select * from t2;

        ID

----------

         1

         2

         3

SQL> with a as (select * from t1),

  2   b as (select * from t2)

  3  select * from t2

  4  union

  5  select * from t1;

        ID

----------

         1

         2

         3

SQL> with a as (select * from t1),

  2  b as (select * from t2)

  3  select * from a

  4  union

  5  select * from b; 

        ID

----------

         1

         2

         3

SQL> with a as (select * from t1),

  2  b as (select * from t2)

  3  select * from a

  4  union

  5  select * from b

  6  where id in (2,3);

        ID

----------

         1

         2

         3

SQL> with a as (select * from t1),

  2  b as (select * from t2)

  3  select * from a

  4  union

  5  select * from b

  6  where id not in (2,3);

        ID

----------

         1

可以看出来where条件只是对 每个SELECT语句的表做限制。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24492954/viewspace-772056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24492954/viewspace-772056/