利用WITH_AS簡化SQL語句與增加效能

有時設計好的SQL語句，總是被SQL本身條件限制住，先來看WITH AS的簡單示範：

system@LOCAL> ed
已將 file afiedt.buf 寫入

1 WITH TEMP_TABLE AS (
2 SELECT 'A' ID,'Alphabetical' DSCR FROM dual UNION ALL
3 SELECT 'B','Board' FROM dual UNION ALL
4 SELECT 'C','Color' FROM dual UNION ALL
5 SELECT 'D','Duck' FROM dual UNION ALL
6 SELECT 'E','English' FROM dual UNION ALL
7 SELECT 'F','Fire' FROM dual UNION ALL
8 SELECT 'G','Good' FROM dual UNION ALL
9 SELECT 'H','High' FROM dual UNION ALL
10 SELECT 'I','Image' FROM dual)
11* select * from TEMP_TABLE
system@LOCAL> /

ID DSCR
-- ------------------------
A Alphabetical
B Board
C Color
D Duck
E English
F Fire
G Good
H High
I Image

已選取 9 個資料列.

system@LOCAL>

在這裡可以看出來WITH AS語法非常適於UNION ALL的SQL語句，例如：

1.需要一個表格卻不常用到，但不希望建立這表格。
2.龐大的資料本身分群後，不管是否使用UNION ALL的方式組合起來的結果集。

這個語法也可以建立多個TEMP TABLE(請注意兩個SUBQUERY中間有個逗點)：

system@LOCAL> ed
已將 file afiedt.buf 寫入

1 WITH TEMP_TABLE1 AS (
2 SELECT 'A' ID,'Alphabetical' DSCR FROM dual UNION ALL
3 SELECT 'B','Board' FROM dual UNION ALL
4 SELECT 'C','Color' FROM dual UNION ALL
5 SELECT 'D','Duck' FROM dual ),
6 TEMP_TABLE2 AS (
7 SELECT 'E','English' FROM dual UNION ALL
8 SELECT 'F','Fire' FROM dual UNION ALL
9 SELECT 'G','Good' FROM dual UNION ALL
10 SELECT 'H','High' FROM dual UNION ALL
11 SELECT 'I','Image' FROM dual)
12 SELECT * FROM TEMP_TABLE1 UNION ALL
13* SELECT * FROM TEMP_TABLE2
system@LOCAL> /

ID DSCR
-- ------------------------
A Alphabetical
B Board
C Color
D Duck
E English
F Fire
G Good
H High
I Image

已選取 9 個資料列.

system@LOCAL>