With temp as---sql語句用法

　　With alias_name as (select1), --as和select中的括號(hào)都不能省略

　　alias_name2 as (select2),--后面的沒有with，逗號(hào)分割，同一個(gè)主查詢同級(jí)別地方，with子查詢只能定義一次

　　…

　　alias_namen as (select n) –與下面的實(shí)際查詢之間沒有逗號(hào)

　　Select ….

　　with使用例子:

　　1.一般使用方式

　　如查詢銷售部門員工的姓名：

　　--with clause

　　with a as

　　(select id from s_dept where name=Sales order by id)

　　select last_name,title

　　from s_emp where dept_id in (select * from a);--使用select查詢別名

　　使用with 子句，可以在復(fù)雜的查詢中預(yù)先定義好一個(gè)結(jié)果集，然后在查詢中反復(fù)使用，不使用會(huì)報(bào)錯(cuò)。而且with 子句獲得的是一個(gè)臨時(shí)表，如果在查詢中使用，必須采用select from with 查詢名，比如

　　With cnt as(select count(*) from table)

　　Select cnt+1 from dual;

　　是錯(cuò)誤的。必須是

　　With cnt as(select count(*) shumu from user_tables)

　　Select shumu+1 from cnt;

　　2.在大多數(shù)子查詢中引用，同級(jí)可見

　　再如下面兩個(gè)語句含義等價(jià)：

　　with a as (select trade_id,name from product) --使用之前定義

　　select name from a where a.trade_id in (

　　with b as (select id from trademark where id=1) --使用之前定義

　　select id from b

　　);

　　select name from product where trade_id in (select id from trademark where id=1);

　　3. with子查詢不可嵌套定義，但是后面的with定義可以引用前面的結(jié)果集。

　　with select_trade as (select trade_id from product where id=1),

　　--后面的with子查詢可以引用前面的結(jié)果

　　select_trademark as (select name from trademark where id=(select trade_id from select_trade))

　　select * from select_trademark;

　　--這條語句錯(cuò)誤

　　with select_trade as

　　--with中有嵌套with,不允許

　　( with temp as ( select trade_id from product where id=1)

　　select trade_id from temp

　　),

　　select_trademark as (select name from trademark where id=(select trade_id from select_trade))

　　select * from select_trademark;

　　4. 同級(jí)定義，只能有一個(gè)with

　　with a as (select trade_id from product),

　　b as (select id from trademark where rownum<3) --第2個(gè)定義不用with

　　select trade_id from a

　　minus

　　select id from b;

5. 在大多數(shù)子查詢中定義和使用，一個(gè)復(fù)雜的例子

　　SELECT a ,b

　　FROM (

　　--第1個(gè)定義t_with

　　WITH

　　t_with AS (SELECT 1 a FROM DUAL)

　　--子查詢使用t_with

　　SELECT x.a ,(

　　--內(nèi)部定義了個(gè)t_with_z,并且使用t_with

　　WITH t_with_z as (SELECT 1 a FROM t_with )

　　SELECT s_1.a FROM t_with_z s_1 ,t_with s_2

　　6. 集合中使用with子查詢

　　--這個(gè)是錯(cuò)誤的，同級(jí)只能有一個(gè)with定義，當(dāng)然兩個(gè)可以寫在一起。

　　SELECT *

　　FROM (

　　WITH

　　t_with_1 AS (SELECT 1 a ,1 b FROM DUAL)

　　--此后的整個(gè)語句被認(rèn)為是一個(gè)主查詢語句

　　SELECT x.a , x.b FROM t_with_1 x

　　union all

　　WITH -- 此處再次出現(xiàn)定義

　　t_with_2 AS (SELECT 1 a ,2 b FROM DUAL)

　　SELECT y.a ,y.b FROM t_with_2 y

　　--正確

　　SELECT *

　　FROM (

　　--第1個(gè)with

　　WITH

　　t_with_1 AS (SELECT 1 a ,1 b FROM DUAL)

　　SELECT x.a , x.b ,x c FROM t_with_1 x

　　union all

　　--這里不能用with再次定義，同級(jí)只能有1個(gè)with,可以寫在最前面

　　--內(nèi)部with

　　SELECT y.a ,y.b , (WITH

　　t_with_x AS (SELECT * FROM t_with_1)

　　SELECT a FROM t_with_x )

　　FROM ( WITH

　　t_with_2 AS (SELECT 1 a ,2 b FROM DUAL)

　　SELECT a ,b FROM t_with_2

　　) y

　　WHERE y.a = (

　　with t_with_3 AS (SELECT 1 a ,2 b FROM DUAL)

　　select a from t_with_3

　　)

　　)

　　7.一個(gè)with查詢的實(shí)例：

　 　查詢出部門的總薪水大于所有部門平均總薪水的部門。部門表s_dept，員工表s_emp。分析：做這個(gè)查詢，首先必須計(jì)算出所有部門的總薪 水，然后計(jì)算出總薪水的平均薪水，再篩選出部門的總薪水大于所有部門總薪水平均薪水的部門。那么第1 步with 查詢查出所有部門的總薪水，第2 步用with 從第1 步獲得的結(jié)果表中查詢出平均薪水，最后利用這兩次的with 查詢比較總薪水大于平均薪水的結(jié)果，如下：

　　with

　　--step1:查詢出部門名和部門的總薪水

　　dept_costs as(

　　select a.name,sum(b.salary) dept_total

　　from

　　s_dept a,s_emp b

　　where a.id=b.dept_id

　　group by a.name

　　),

　　--step2:利用上一個(gè)with查詢的結(jié)果，計(jì)算部門的平均總薪水

　　avg_costs as(

　　select sum(dept_total)/count(*) dept_avg

　　from dept_costs

　　)

　　--step3:從兩個(gè)with查詢中比較并且輸出查詢結(jié)果

　　select name,dept_total

　　from dept_costs

　　where

　　dept_total>

　　(

　　select dept_avg

　　from

　　avg_costs

　　)

　　order by name;

　　從上面的查詢可以看出，前面的with 查詢的結(jié)果可以被后面的with查詢重用，并且對(duì)with 查詢的結(jié)果列支持別名的使用，在最終查詢中必須要引用所有with 查詢，否則會(huì)報(bào)錯(cuò)ora-32035 錯(cuò)誤。

　　再如有這樣一個(gè)需求：一個(gè)查詢，如果查詢的結(jié)果行不滿足是10 的倍數(shù)，則補(bǔ)空行，直到是查詢出的行數(shù)是10 的倍數(shù)。例如：select * from trademark這個(gè)查詢。

3.2 11G R2with新特性

　　新版本的WITH 允許遞歸使用，在功能上比原來有了質(zhì)的飛躍，可以實(shí)現(xiàn)許多原來CONNECT BY做不到的事。

　　3.3 with對(duì)執(zhí)行計(jì)劃的影響

　　with子句有可能會(huì)改變執(zhí)行計(jì)劃。