用一個sql語句完成一下問題：
??? A,B,C三人的年齡相乘為36；相加等于所有在場的人的人數(shù)；年齡大的人的寵物倉鼠的有個腿是木頭的，試著給出這三人的年齡。
??? 解決方案：
??? 需要知道用于查詢分解的基本格式，考慮類似下面的表達式
??? with
alias1 as (subQuery1)
alias2 as(subQuery2)
??????? ……
?? select
??????? ……
；
??? 首先，注意帶三人年齡的乘積是36，可設(shè)年齡都是整數(shù)，因此就必須創(chuàng)建一個1~36的范圍內(nèi)，——就需要一個36行的表，每一個表對應(yīng)一個可能的年齡：
??? with age_list as
??? {
??? select rowNum as age from dual where rowNum<=36;
??? }，
???? 是三個人，需要創(chuàng)建三個副本，同時年齡乘積是36——之后還要把這3個年齡相加，下面同時進行。注意前面的SQL結(jié)尾使用是一個逗號，下面的部分就簡單地以一個新的別名開始（不再重復(fù)with）：
???? product_check as
????? {
?????????? select
????????????????????? age1.age as youngest,
????????????????????? age2.age as middle,
????????????????????? age3.age as oldest
????????????????????? age1.age+age2.age+age3.age as sumed
?????????? from age_list age1,age_list2 age2,age_list age3
?????????? where age1.age<=age2.age and age2.age<=age3.age?? //保證每種組合值出現(xiàn)一次
????????????????????? and age1.age*age2.age*age3.age=36
???? }
??? 得出的結(jié)果是
??? Y???????????? M????????? O??????????? S
——————————————————————————
??? 1????????????? 1?????????? 36?????????? 38
??? 1????????????? 2?????????? 18?????????? 21
??? 1????????????? 3?????????? 12?????????? 16
??? 1????????????? 4???????? ?? 9???????????? 14
??? 1????????????? 6????????? 6???????????? 13
??? 2????????????? 2??????????? 9???????????? 13
??? 2????????????? 3??????????? 6???????????? 11
??? 3????????????? 3?????? ?? 4 10 ???
?? 現(xiàn)在 年齡之和等于一個數(shù)，知道了現(xiàn)場人數(shù)仍不能確定，——說明查詢集合的和至少有兩行的值是一樣的。
?? 縮小輸出范圍
??? summed_checked as
???? {
?????????? select youngest, middle, oldest ,sumed
?????????? from
??????????????? { select youngest,middle,oldest,sumed,count (*) over (partition by summed) cnt
??????????????????? from???? product_chect
??????????????? }
?????????? where cnt>=2
?? }
?? 輸出結(jié)果：
??? Y????????????? M????????????? O????????????? S
————————————————————————————
??? 1????????????? 6????????????? 6????????????? 13
??? 2????????????? 2????????????? 9????????????? 13
??? 接著“年齡大的人的寵物倉鼠有個腿是木質(zhì)的”，表明年齡大的人的年齡比中間那個要大。
??? 所以
??? select yongest,middle,oldest from sumed_checked
where oldest>middle
??? 得出結(jié)果：
??? Y????????????? M????????????? O????????????? S
————————————————————————————
??? 2????????????? 2????????????? 9????????????? 13
??

完整的查詢?nèi)缦?在oracle9.2.0.1中測試通過)：
with
age_list as
?? (select rowNum age from all_all_tables where rownum<=36),
product_check as
(
??? select
?????? age1.age youngest,
?????? age2.age middle,
?????? age3.age oldest,
?????? age1.age+age2.age+age3.age as sumed
??? from age_list age1,age_list age2,age_list age3
??? where age1.age<=age2.age and age2.age<=age3.age
??? and age1.age*age2.age*age3.age=36
),
sumed_check as
(
?? select youngest,middle,oldest,sumed
?? from
????? (
????????? select youngest,
???????????????? middle,
???????????????? oldest,
???????????????? sumed,
???????????????? count(*) over(partition by sumed) cnt
????????? from product_check???????????????
????? )
?? where cnt>=2??
)
select youngest,middle,oldest
from sumed_check
where middle<oldest

----------------------------------------------------------------
oracle 中with的用法
????? 當查詢中多次用到某一部分時，可以用with語句創(chuàng)建一個公共臨時表。因為子查詢在內(nèi)存臨時表中，避免了重復(fù)解析，所以執(zhí)行效率會提高不少。臨時表在一次查詢結(jié)束自動清除。
?????? 一般語法格式：
?????? with
alias_name1 as??? (subquery1),
??????????? alias_name2 as??? (subQuery2),
??????????? ……
??????????? alias_nameN as??? (subQueryN)
????? select col1,col2…… col3
???????????????? from alias_name1,alias_name2……,alias_nameN

????? 例子：
????? SQL> WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
??????????????? Q2 AS (SELECT 3 * 5 M FROM DUAL),
??????????????? Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
輸出結(jié)果：
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120

???? 所有的子查詢都可以用到，下面是轉(zhuǎn)帖網(wǎng)上的一個UPDATE語句(url:http://www.oracle.com.cn/viewthread.php?tid=83530)：（

在討論一個有關(guān)表的UPDATE時, 寫了如下的SQL:<原始需求,請參考: http://www.oracle.com.cn/viewthr ... ghlight=&page=2 >

SQL> update test2 set spc = (
?? 2 ?? ?? select substr(max(sys_connect_by_path(b.name, '-')),2) name
?? 3 ?? ?? ?? from (select rn, skycode id,
?? 4 ?? ?? ?? ?? ?? ?? ?? ??? decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
?? 5 ?? ?? ?? ?? ?? ?? ?? ??? decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1, instr(skycode, '-', 1, rn)) ep
?? 6 ?? ?? ?? ?? ?? ?? from (select rownum rn from dual connect by rownum<=20) a, test2 b
?? 7 ?? ?? ?? ?? ?? ?? where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0
?? 8 ?? ?? ?? ?? ?? ) a, test b
?? 9 ?? ?? ?? where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
10 ?? ?? ?? start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );

當時是在9.2.0.7下做的,沒有問題,可以有朋友在9.2.0.1下,就會出現(xiàn):

ORA-03113:通信通道文件結(jié)束
ORA-03114:未連接ORACLE

的錯誤提示, 這是9.2.0.1的一個BUG, 在多次的子查詢時出現(xiàn), 我試過,在9.2.0.5已經(jīng)沒有了,但不知道從那個版本ORACLE做了更正.

前段時間在寫類似的多子查詢的SELECT語句時, ORACLE9I提供的一個新子句: WITH在某種程度上解決了部分這類錯誤的出現(xiàn). 經(jīng)測試,原來同樣的寫法,也可以用于UPDATE中, 如上面的語句,可以用WITH改寫為:

SQL> update test2 set spc = (
?? 2 ?? ?? with myque as (select rn, skycode id,
?? 3 ?? ?? ?? ?? ?? ?? ?? ??? decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + 1 sp,
?? 4 ?? ?? ?? ?? ?? ?? ?? ??? decode(instr(skycode, '-', 1, rn), 0, length(skycode) + 1,
?? 5 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? instr(skycode, '-', 1, rn)) ep
?? 6 ?? ?? ?? ?? ?? ?? ??? from (select rownum rn from dual connect by rownum<=20) a, test2 b
?? 7 ?? ?? ?? ?? ?? ?? ??? where decode(rn, 1, 0, instr(skycode, '-', 1, rn-1)) + instr(skycode, '-', 1, rn) > 0 )
?? 8 ?? ?? select substr(max(sys_connect_by_path(b.name, '-')),2) name
?? 9 ?? ?? ?? from myque a, test b
10 ?? ?? ?? where b.citycode = substr(a.id, sp, ep-sp) and a.id=test2.skycode
11 ?? ?? ?? start with rn=1 connect by rn-1=prior rn and a.id = prior a.id );

已更新4行。

可見, ORACLE在支持子查詢的地方,同時也支持WITH的操作, 本人認為,這樣一來,可以讓開發(fā)人員有更多的機會,寫出高效的單個SQL語句. 特別是在多個子查詢中多次對同一基表進行訪問時.

這是本人的更解, 不知對否, 請各位大蝦批評指正.

oracle子查詢分解（分而治之）