1、with table as 相當(dāng)于建個(gè)臨時(shí)表（用于一個(gè)語(yǔ)句中某些中間結(jié)果放在臨時(shí)表空間的SQL語(yǔ)句），Oracle 9i 新增WITH語(yǔ)法，可以將查詢中的子查詢命名，放到SELECT語(yǔ)句的最前面。
語(yǔ)法就是
with tempname as (select ....)
select ...
例子：
with t as (select * from emp where depno=10)
select * from t where empno=xxx
with
wd as (select did,arg(salary) 平均工資 from work group by did),
em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid)
select * from wd,em where wd.did =em.did and wd.平均工資>em.salary;
2、何時(shí)被清除
臨時(shí)表不都是會(huì)話結(jié)束就自動(dòng)被PGA清除嘛! 但with as臨時(shí)表是查詢完成后就被清除了！
23:48:58 SCOTT@orcl> with aa as(select * from dept)
23:57:58 2 select * from aa;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
已用時(shí)間: 00: 00: 00.12
23:58:06 SCOTT@orcl> select * from aa;
select * from aa
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00942: 表或視圖不存在
已用時(shí)間: 00: 00: 00.02
23:58:14 SCOTT@orcl>
3、就這一功能來(lái)說(shuō)，子查詢就可以達(dá)到啊，為什么要用with呢？ 用with有什么好處？
都能寫，但執(zhí)行計(jì)劃不同的。當(dāng)有多個(gè)相似子查詢的時(shí)候，用with寫公共部分，因?yàn)樽硬樵兘Y(jié)果在內(nèi)存臨時(shí)表中，執(zhí)行效率當(dāng)然就高啦~
4、問(wèn)題：
有張表數(shù)據(jù)如下：
aaa 高
bbb 低
aaa 低
aaa 高
bbb 低
bbb 高
需要得到下列結(jié)果,
高 低
aaa 2 1
bbb 1 2
問(wèn) SQL 語(yǔ)句怎么寫？？
答案：
with tt as (
select 'aaa' id, '高' value from dual union all
select 'bbb' id, '低' value from dual union all
select 'aaa' id, '低' value from dual union all
select 'aaa' id, '高' value from dual union all
select 'bbb' id, '低' value from dual union all
select 'bbb' id, '高' value from dual)
SELECT id,
COUNT(decode(VALUE, '高', 1)) 高,
COUNT(decode(VALUE, '低', 1)) 低
FROM tt
GROUP BY id;
===================================================================
擴(kuò)展：
Oracle9i新增WITH語(yǔ)法，可以將查詢中的子查詢命名，放到SELECT語(yǔ)句的最前面。
一個(gè)簡(jiǎn)單的例子:
SQL> WITH
2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),
3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)
4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K
5 FROM OBJ O, SEG S
6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)
7 ;
OBJECT_NAME OBJECT_TYPE SIZE_K
------------------------------ ------------------- ----------
DAIJC_TEST TABLE 128
P_TEST PROCEDURE 0
IND_DAIJC_TEST_C1 INDEX 128
通過(guò)WITH語(yǔ)句定義了兩個(gè)子查詢SEG和OBJ，在隨后的SELECT語(yǔ)句中可以直接對(duì)預(yù)定義的子查詢進(jìn)行查詢。從上面的例子也可以看出，使用WITH語(yǔ)句，將一個(gè)包含聚集、外連接等操作SQL清晰的展現(xiàn)出來(lái)。
WITH定義的子查詢不僅可以使查詢語(yǔ)句更加簡(jiǎn)單、清晰，而且WITH定義的子查詢還具有在SELECT語(yǔ)句的任意層均可見的特點(diǎn)。
即使是在WITH的定義層中，后定義的子查詢都可以使用前面已經(jīng)定義好的子查詢:
SQL> WITH
2 Q1 AS (SELECT 3 + 5 S FROM DUAL),
3 Q2 AS (SELECT 3 * 5 M FROM DUAL),
4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
5 SELECT * FROM Q3;
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120
利用WITH定義查詢中出現(xiàn)多次的子查詢還能帶來(lái)性能提示。Oracle會(huì)對(duì)WITH進(jìn)行性能優(yōu)化，當(dāng)需要多次訪問(wèn)WITH定義的子查詢時(shí)，Oracle會(huì)將子查詢的結(jié)果放到一個(gè)臨時(shí)表中，避免同樣的子查詢多次執(zhí)行，從而有效的減少了查詢的IO數(shù)量。
WITH能用在SELECT語(yǔ)句中，UPDATE和DELETE語(yǔ)句也是支持WITH語(yǔ)法的，只是需要版本支持:
http://www.oracle.com.cn/viewthread.php?tid=83530
=============================================================================
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);
再舉個(gè)簡(jiǎn)單的例子
with a as (select * from test)
select * from a;
其實(shí)就是把一大堆重復(fù)用到的SQL語(yǔ)句放在with as 里面，取一個(gè)別名，后面的查詢就可以用它
這樣對(duì)于大批量的SQL語(yǔ)句起到一個(gè)優(yōu)化的作用，而且清楚明了
這是搜索到的英文文檔資料(說(shuō)得比較全,但是本人英文特菜,還沒(méi)具體了解到,希望各高手具體談?wù)勥@個(gè)with
as 的好處)
About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
? The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
? Formally, the “WITH clause” is called subquery factoring
? The SQL “WITH clause” is used when a subquery is executed multiple times
? Also useful for recursive queries (SQL-99, but not Oracle SQL)
To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:
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”:
WITH
sum_sales AS
select /*+ materialize */
sum(quantity) all_sales from stores
number_stores AS
select /*+ materialize */
count(*) nbr_stores from stores
sales_by_store AS
select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores)
;
Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.
It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.
To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:
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”:
posted on 2011-12-06 16:47
紫蝶∏飛揚(yáng)↗ 閱讀(23197)
評(píng)論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫(kù)