用一個(gè)sql語(yǔ)句完成一下問(wèn)題：
??? A,B,C三人的年齡相乘為36；相加等于所有在場(chǎng)的人的人數(shù)；年齡大的人的寵物倉(cāng)鼠的有個(gè)腿是木頭的，試著給出這三人的年齡。
??? 解決方案：
??? 需要知道用于查詢(xún)分解的基本格式，考慮類(lèi)似下面的表達(dá)式
??? with
alias1 as (subQuery1)
alias2 as(subQuery2)
??????? ……
?? select
??????? ……
；
??? 首先，注意帶三人年齡的乘積是36，可設(shè)年齡都是整數(shù)，因此就必須創(chuàng)建一個(gè)1~36的范圍內(nèi)，——就需要一個(gè)36行的表，每一個(gè)表對(duì)應(yīng)一個(gè)可能的年齡：
??? with age_list as
??? {
??? select rowNum as age from dual where rowNum<=36;
??? }，
???? 是三個(gè)人，需要?jiǎng)?chuàng)建三個(gè)副本，同時(shí)年齡乘積是36——之后還要把這3個(gè)年齡相加，下面同時(shí)進(jìn)行。注意前面的SQL結(jié)尾使用是一個(gè)逗號(hào)，下面的部分就簡(jiǎn)單地以一個(gè)新的別名開(kāi)始（不再重復(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)在 年齡之和等于一個(gè)數(shù)，知道了現(xiàn)場(chǎng)人數(shù)仍不能確定，——說(shuō)明查詢(xún)集合的和至少有兩行的值是一樣的。
?? 縮小輸出范圍
??? 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
??? 接著“年齡大的人的寵物倉(cāng)鼠有個(gè)腿是木質(zhì)的”，表明年齡大的人的年齡比中間那個(gè)要大。
??? 所以
??? select yongest,middle,oldest from sumed_checked
where oldest>middle
??? 得出結(jié)果：
??? Y????????????? M????????????? O????????????? S
————————————————————————————
??? 2????????????? 2????????????? 9????????????? 13
??

完整的查詢(xún)?nèi)缦?在oracle9.2.0.1中測(cè)試通過(guò))：
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的用法
????? 當(dāng)查詢(xún)中多次用到某一部分時(shí)，可以用with語(yǔ)句創(chuàng)建一個(gè)公共臨時(shí)表。因?yàn)樽硬樵?xún)?cè)趦?nèi)存臨時(shí)表中，避免了重復(fù)解析，所以執(zhí)行效率會(huì)提高不少。臨時(shí)表在一次查詢(xún)結(jié)束自動(dòng)清除。
?????? 一般語(yǔ)法格式：
?????? 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

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

在討論一個(gè)有關(guān)表的UPDATE時(shí), 寫(xiě)了如下的SQL:<原始需求,請(qǐng)參考: 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 );

當(dāng)時(shí)是在9.2.0.7下做的,沒(méi)有問(wèn)題,可以有朋友在9.2.0.1下,就會(huì)出現(xiàn):

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

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

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

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行。

可見(jiàn), ORACLE在支持子查詢(xún)的地方,同時(shí)也支持WITH的操作, 本人認(rèn)為,這樣一來(lái),可以讓開(kāi)發(fā)人員有更多的機(jī)會(huì),寫(xiě)出高效的單個(gè)SQL語(yǔ)句. 特別是在多個(gè)子查詢(xún)中多次對(duì)同一基表進(jìn)行訪(fǎng)問(wèn)時(shí).

這是本人的更解, 不知對(duì)否, 請(qǐng)各位大蝦批評(píng)指正.

oracle子查詢(xún)分解（分而治之）