oracle with as 用法

標籤：oracle   with as   查詢結果重複使用   

With查詢語句已with開頭，相當於在查詢之前先構建一個暫存資料表，被指定的查詢結果存與暫存資料表中，之後便可多次使用它做進一步的分析和處理。

文法：

with _tempTable as (select * from table )

select  * from _tempTable

例子：

with _tempStudent as(

select * from t_student t where class = '初二3班'

)

select sex,count(1) nums from _tempStudent where sex = '男' and height > '170'

union all

select sex,count(1) nums from _tempStudent where sex = '女' and height > '160'

多個with as 用法 每個臨時存量直接用 "," 隔開

with t1 as (

     select * from student where name in('張三','李四')

),t2 as (

     select * from student where name in('王五')

)

select * from t1

union 

select * from t2

如果with as 有嵌套的情況， 多個with as，後面的as內部可以直接調用先聲明的臨時對象

with t1 as (

     select * from student where name in('張三','李四')

),t2 as (

     select * from t1 where name in('王五')

)

select * from t2

oracle with as 用法