|
oracle with as 用法
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”:
=================================================================================
下面自己小试一把，当然，一点都不复杂，很简单很简单的例子，呵呵。
view plaincopy to clipboardprint?
SQL> create table t2(id int);
Table created.
SQL> create table t3(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 t3 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t2;
ID
----------
1
2
SQL> select * from t3;
ID
----------
3
SQL> with
2 sql1 as (select * from t2),
3 sql2 as (select * from t3)
4 select * from t2
5 union
6 select * from t3;
sql2 as (select * from t3)
*
ERROR at line 3:
ORA-32035: unreferenced query name defined in WITH clause
--从这里可以看到，你定义了sql1和sql2，就得用它们哦，不然会报错的。
SQL> with
2 sql1 as (select * from t2),
3 sql2 as (select * from t3)
4 select * from sql1
5 union
6 select * from sql2;
ID
----------
1
2
3
--下面加个WHERE条件试试
SQL> with
2 sql1 as (select * from t2),
3 sql2 as (select * from t3)
4 select * from sql1
5 union
6 select * from sql2
7 where id in(2,3);
ID
----------
1
2
3
--奇怪？为什么加了WHERE条件还是输出ID=1的记录了，继续往下看：
SQL> with
2 sql1 as (select * from t2),
3 sql2 as (select * from t3)
4 select * from sql1
5 where id=3
6 union
7 select * from sql2
8 where id=3;
ID
----------
3
--可以看到，每个条件是要针对每个SELECT语句的。
SQL> create table t2(id int);
Table created.
SQL> create table t3(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 t3 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t2;
ID
----------
1
2
|
