Oracle中with?as、START?with?...?CONNECT?by?PRIOR?(CONNECT_BY_ROOT)的用法
一?、with?as?
(一)含義
WITH?AS查詢語句，也叫子查詢部分（subquery?factoring），定義一個SQL共用片斷，該SQL片斷會被整個SQL語句所用到。?它不是以select開始，而是以“WITH”關(guān)鍵字開頭。在真正進(jìn)行查詢之前預(yù)先構(gòu)造了一個臨時表，之后便可多次使用它做進(jìn)一步的分析和處理。
(二)語法：每個子查詢以逗號分隔，要注意：最后一個沒有任何符號。
with?query1?AS
(select?...from?....where?..),
query2??AS
(select...from?...where..),
query3?AS
(select...from?...where..)
SELECT?...FROM?query1,quer2,query3?
where?....;
(三)用途
1、提高SQL語句可讀性。
??增加了SQL的易讀性，如果構(gòu)造了多個子查詢，結(jié)構(gòu)會更清晰。
2、提高執(zhí)行效率。
with?as在查詢的時候建立臨時表，數(shù)據(jù)是寫入了內(nèi)存中?！耙淮畏治?，多次使用”，達(dá)到提高sql執(zhí)行性能的地方，達(dá)到了“少讀”的目標(biāo)。
如果WITH?AS短語所定義的表名被調(diào)用兩次以上，則優(yōu)化器會自動將WITH?AS短語所獲取的數(shù)據(jù)放入一個TEMP表里，如果只是被調(diào)用一次，則不會。而提示materialize則是強(qiáng)制將WITH?AS短語里的數(shù)據(jù)放入一個全局臨時表里。很多查詢通過這種方法都可以提高速度。
二、?遞歸查詢?start?with?...?CONNECT?by?PRIOR
（一）含義
一般用來查找存在父子關(guān)系的數(shù)據(jù)，也就是樹形結(jié)構(gòu)的數(shù)據(jù)；其返還的數(shù)據(jù)也能夠明確的區(qū)分出每一層的數(shù)據(jù)。
（二）語法
SELECT?...?FROM????+?表名
WHERE??????????????+?條件3
START?WITH?????????+?條件1
CONNECT?BY?PRIOR???+?條件2
--示例
Select?*?From?DEMO
Start?With?ID?=?'00001'
Connect?By?Prior?ID?=?PID
start?with?condition1?是用來限制第一層的數(shù)據(jù)，或者叫根節(jié)點(diǎn)數(shù)據(jù)；以這部分?jǐn)?shù)據(jù)為基礎(chǔ)來查找第二層數(shù)據(jù)，然后以第二層數(shù)據(jù)查找第三層數(shù)據(jù)以此類推。
connect?by?[prior]?id=parentid?這部分是用來指明oracle在查找數(shù)據(jù)時以怎樣的一種關(guān)系去查找；比如說查找第二層的數(shù)據(jù)時用第一層數(shù)據(jù)的id去跟表里面記錄的parentid字段進(jìn)行匹配，如果這個條件成立那么查找出來的數(shù)據(jù)就是第二層數(shù)據(jù)，同理查找第三層第四層…等等都是按這樣去匹配。
條件1:?表示從哪個節(jié)點(diǎn)開始查找,?也就是通過條件1?查詢到的數(shù)據(jù),?作為后續(xù)查詢的起始節(jié)點(diǎn)(參數(shù))。
當(dāng)然可以放寬限定條件，如?ID?in?('00001',?'00011')以取得多個根節(jié)點(diǎn)，也就是多棵樹；在連接關(guān)系中，除了可以使用列明外，還允許使用列表達(dá)式。
如果省略Start?With
就默認(rèn)把所有滿足查詢條件的Tree整個表中的數(shù)據(jù)從頭到尾遍歷一次,每一個數(shù)據(jù)做一次根,然后遍歷樹中其他節(jié)點(diǎn)信息。
條件2:?是連接條件，其中用PRIOR表示上一條記錄，例如CONNECT?BY?PRIOR?ID?=?PID，意思就是上一條記錄的ID是本條記錄的PID，即本記錄的父親是上一條記錄。CONNECT?BY子句說明每行數(shù)據(jù)將是按照層次順序檢索，并規(guī)定將表中的數(shù)據(jù)連入樹形結(jié)構(gòu)的關(guān)系中。
Prior?在父節(jié)點(diǎn)的一側(cè)表示,?自底向上查,?在?子節(jié)點(diǎn)的一側(cè)表示?自上向下查詢;
條件3:?不能用在?Connect?By?后,?這里的條件判斷,?等價(jià)于?在最后查詢出結(jié)果列表之后,?再進(jìn)行條件篩選;?并非?刪除掉?節(jié)點(diǎn)及子節(jié)點(diǎn);
（三）語法變種
select?*?from?table?[start?with?condition1]
????connect?by?[prior]?id=parentid
select?*?from?table?[start?with?condition1]
????connect?by?id=?[prior]?parentid
這種用法就表示從下往上查找數(shù)據(jù)，可以理解為從葉子節(jié)點(diǎn)往上查找父級幾點(diǎn)，用第一層數(shù)據(jù)的parentid去跟表記錄里面的id進(jìn)行匹配，匹配成功那么查找出來的就是第二層數(shù)據(jù)；上面的那種就是從父級節(jié)點(diǎn)往下查找葉子節(jié)點(diǎn)。
（四）其他特性
1、level關(guān)鍵字，表示當(dāng)前節(jié)點(diǎn)所處層級,?這里的層級指的是?從?start?with?查詢到的節(jié)點(diǎn)開始往下算起,?當(dāng)前屬于第幾層級。第一層是數(shù)字1，第二層數(shù)字2，依次遞增。
2、CONNECT_BY_ROOT方法，能夠獲取第一層集結(jié)點(diǎn)結(jié)果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。
3、nocycle關(guān)鍵字,?有時候數(shù)據(jù)本身?不合理會導(dǎo)致出現(xiàn)循環(huán)的問題,?如?將上述的?ID?'00001'?記錄的?'PID'?也改為?'00001',?會出現(xiàn)循環(huán)的問題,?這是,?需要用到?nocycle?即可消除循環(huán);
Connect?By?nocycle?Prior?ID?=?PID?即可。
4、connect_by_isleaf?表示當(dāng)前節(jié)點(diǎn)是否是葉子節(jié)點(diǎn)
Select?ID,?PID,?DSC,
connect_by_isleaf?isLeaf,
LEVEL
From?DEMO
Connect?By?nocycle?Prior?ID?=?PID
Start?With?ID?=?'00001';
--結(jié)果
ID??????PID?????DSC??isLeaf?????LEVEL
00001???00001???中國??0???????????0
00011???00001???陜西??0???????????1
00111???00011???西安??1???????????2
00112???00011???咸陽??1???????????2
00113???00011???延安??1???????????2
00012???00001???貴州??1???????????1
00013???00001???河南??1???????????1
另外一點(diǎn):?如果在查詢語句中?Select?ID,?PID,?DSC,?connect_by_isleaf?isLeaf,?LEVEL?-?1?LEVEL?這種查詢方式的話,?在?WHERE?判斷條件中,?只需要判斷?LEVEL?=?1,?就可以取出?當(dāng)前查詢節(jié)點(diǎn)的?子節(jié)點(diǎn)(由于LEVEL?也是?偽列,?需要用子查詢的方式);
（五）start?with?,connect?by?prior其他變形
上面講的用START?WITH?指定樹的根,然后用CONNECT?BY指定遞歸條件.是最簡單的也是最常用的形式.但實(shí)際上還有些變形.
1.START?WITH?可以省略
比如
SELECT?son?FROM?tree
CONNECT?BY?PRIOR?son?=?father;
此時不指定樹的根的話,就默認(rèn)把Tree整個表中的數(shù)據(jù)從頭到尾遍歷一次,每一個數(shù)據(jù)做一次根,然后遍歷樹中其他節(jié)點(diǎn)信息.
在這個例子中,上面的SQL等價(jià)于
SELECT?son?FROM?tree
START?WITH?father?IN?(爺爺,爸爸,兒子,孫子NB,孫子SB)
CONNECT?BY?PRIOR?son?=?father;
那查詢到的結(jié)果如下,有很多重復(fù)信息的
爸爸,兒子,孫子NB,孫子SB????兒子,孫子NB,孫子SB????孫子NB,孫子SB
2.START?WITH?與CONNECT?BY?PRIOR位置可互換
SELECT?son?FROM?tree
CONNECT?BY?PRIOR?son?=?father
START?WITH?father?=?'爺爺';
3.nocycle關(guān)鍵字
我們知道標(biāo)準(zhǔn)的樹結(jié)構(gòu)中是不會有環(huán)的,但表中的樹形結(jié)構(gòu)不是標(biāo)準(zhǔn)的,有可能導(dǎo)致環(huán)的出現(xiàn)
比如
?????????????---------孫子SB
?????????????|??????????????^
?????????????|??????????????|
爺爺?-->?爸爸?-->?兒子?-->孫子NB
哎在這里想用線條整個箭頭出來真他媽麻煩啊.我又有點(diǎn)懶不想用其他畫圖工具啥的啊.反正假設(shè)兒子的兒子是孫子SB?,而孫子SB的兒子是爸爸.這樣就形成一個環(huán)了.
當(dāng)然在Oracle中的role是禁止出現(xiàn)循環(huán)的.比如你grant?A?to?B?,grant?B?to?C?.再來個grant?C?to?A會出錯的.
假如有個上面的環(huán),在再使用開始的遞歸查詢語言會出錯.得用nocycle關(guān)鍵字指定忽略環(huán).
SELECT?son?FROM?tree
START?WITH?father?=?'爺爺'
CONNECT?BY?NOCYCLE?PRIOR?son?=?father;
此時結(jié)果是
爸爸?兒子?孫子NB
你會注意到由于忽略了環(huán),所以孫子SB的信息也被忽略掉了.
4.?connect?by?prior?后面條件順序的改變?
SELECT?son?FROM?tree
START?WITH?father?=?'爺爺'
CONNECT?BY?PRIOR?son?=?father;
這是開頭的寫法,但實(shí)際上也可以寫成father?=?son倒過來寫。有人說沒倒過來是從上到下,從根往下.如果倒過來則是從下到上。
5.還可以加where條件
可以把start?with?,connect?假裝看成where?條件一樣.所以在這個sql語句其他地方還可以加其他where?語句,可以看成與遞歸查詢無關(guān).只是對整個結(jié)果起過濾作用
比如
SELECT?son?FROM?tree??WHERE?son?=?'孫子SB'
START?WITH?father?=?'爺爺'
CONNECT?BY?PRIOR?son?=?father;
--代碼演示
--獲取首條評論
with?fatherComment?as
?(select?t.add_time,
?????????t.comm_content,
?????????decode(t.top_target_type,
????????????????'image',
????????????????'單圖',
????????????????'post',
????????????????'帖子',
????????????????'article',
????????????????'文章',
????????????????'其它')?productType,
?????????t.target_type,
?????????t.target_id,
?????????t.comment_id,
?????????t.device_id
????from?dbread.t_usercomment?t
???where?t.top_target_type?not?in
?????????('comic',?'comic_part',?'cartoon',?'cartoon_part')
?????and?t.target_type?!=?'comment'
?????and?t.add_time?>=?date?'2021-6-1'
?????and?t.add_time?<?date?'2021-6-10'
???order?by?t.add_time?desc?),
--獲取評論的評論：使用start?with...?connect?by?prior獲取父子層級樹狀數(shù)據(jù)
childComment?as
?(select?*
????from?(select?t.add_time,
?????????????????t.comm_content,
?????????????????t.target_id,
?????????????????t.comment_id,
?????????????????t.device_id,
?????????????????LEVEL?lev,?--評論在結(jié)構(gòu)樹中的層級
?????????????????CONNECT_BY_ROOT(t.comment_id)?fatherCommentID?--首條評論的id
????????????from?dbread.t_usercomment?t
???????????WHERE?t.top_target_type?not?in
?????????????????('comic',?'comic_part',?'cartoon',?'cartoon_part')
?????????????and?t.target_type?=?'comment'
?????????????and?t.add_time?>=?date?'2021-6-1'
?????????????and?t.add_time?<?date?'2021-6-10'
???????????START?with?t.target_type?!=?'comment'
??????????????????and?t.comment_id?is?not?null
??????????CONNECT?by?PRIOR?t.comment_id?=?t.target_id
???????????)?t
???where?exists
???(select?1
????????????from?dbread.t_usercomment?tmp1
???????????where?t.device_id?=?tmp1.device_id
?????????????and?trunc(t.add_time)?=?trunc(tmp1.add_time)
?????????????and?tmp1.add_time?>=?date?'2021-6-1'
?????????????and?tmp1.add_time?<?date?'2021-6-10')),
--評論回復(fù)數(shù)
commentReply?as
?(select?t.fatherCommentID,?count(1)?replyCount
????from?childComment?t
??--where?condition
???group?by?t.fatherCommentID)
--最終結(jié)果
select?t.add_time?發(fā)布時間,
???????t.comm_content?評論內(nèi)容,
???????t.productType?產(chǎn)品類別,
???????nvl(t1.replyCount,?0)?評論回復(fù)數(shù),
???????nvl(t2.praiseCount,?0)?評論獲贊數(shù)
??from?fatherComment?t
??left?join?commentReply?t1
????on?t.comment_id?=?t1.fatherCommentID
??left?join?(select?t.target_id,?count(1)?praiseCount
???????????????from?dbread.t_userPraise?t
??????????????where?t.oper_type?in?('01',?'03',?'04')
????????????????and?t.target_type?=?'comment'
????????????????and?t.add_time?>=?date?'2021-6-1'
????????????????and?t.add_time?<?date?'2021-6-10'
??????????????group?by?t.target_id)?t2
????on?t.comment_id?=?t2.target_id;
