Oracle中with as耀石、START with ... CONNECT by PRIOR (CONNECT_BY_ROOT)的用法

Oracle中with?as力崇、START?with?...?CONNECT?by?PRIOR?(CONNECT_BY_ROOT)的用法

一?粪牲、with?as?

(一)含義

WITH?AS查詢語句古瓤,也叫子查詢部分(subquery?factoring),定義一個SQL共用片斷腺阳,該SQL片斷會被整個SQL語句所用到落君。?它不是以select開始,而是以“WITH”關(guān)鍵字開頭亭引。在真正進行查詢之前預先構(gòu)造了一個臨時表绎速,之后便可多次使用它做進一步的分析和處理。

(二)語法:每個子查詢以逗號分隔焙蚓,要注意:最后一個沒有任何符號纹冤。

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)存中求妹〗咸常“一次分析,多次使用”扒最,達到提高sql執(zhí)行性能的地方丑勤,達到了“少讀”的目標。

如果WITH?AS短語所定義的表名被調(diào)用兩次以上吧趣,則優(yōu)化器會自動將WITH?AS短語所獲取的數(shù)據(jù)放入一個TEMP表里法竞,如果只是被調(diào)用一次,則不會强挫。而提示materialize則是強制將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é)點數(shù)據(jù);以這部分數(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字段進行匹配抡四,如果這個條件成立那么查找出來的數(shù)據(jù)就是第二層數(shù)據(jù)柜蜈,同理查找第三層第四層…等等都是按這樣去匹配。

條件1:?表示從哪個節(jié)點開始查找,?也就是通過條件1?查詢到的數(shù)據(jù),?作為后續(xù)查詢的起始節(jié)點(參數(shù))指巡。

當然可以放寬限定條件淑履,如?ID?in?('00001',?'00011')以取得多個根節(jié)點,也就是多棵樹藻雪;在連接關(guān)系中秘噪,除了可以使用列明外,還允許使用列表達式阔涉。

如果省略Start?With

就默認把所有滿足查詢條件的Tree整個表中的數(shù)據(jù)從頭到尾遍歷一次,每一個數(shù)據(jù)做一次根,然后遍歷樹中其他節(jié)點信息缆娃。

條件2:?是連接條件捷绒,其中用PRIOR表示上一條記錄,例如CONNECT?BY?PRIOR?ID?=?PID贯要,意思就是上一條記錄的ID是本條記錄的PID暖侨,即本記錄的父親是上一條記錄。CONNECT?BY子句說明每行數(shù)據(jù)將是按照層次順序檢索崇渗,并規(guī)定將表中的數(shù)據(jù)連入樹形結(jié)構(gòu)的關(guān)系中字逗。

Prior?在父節(jié)點的一側(cè)表示,?自底向上查,?在?子節(jié)點的一側(cè)表示?自上向下查詢;

條件3:?不能用在?Connect?By?后,?這里的條件判斷,?等價于?在最后查詢出結(jié)果列表之后,?再進行條件篩選;?并非?刪除掉?節(jié)點及子節(jié)點;

(三)語法變種

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é)點往上查找父級幾點宅广,用第一層數(shù)據(jù)的parentid去跟表記錄里面的id進行匹配葫掉,匹配成功那么查找出來的就是第二層數(shù)據(jù);上面的那種就是從父級節(jié)點往下查找葉子節(jié)點跟狱。

(四)其他特性

1俭厚、level關(guān)鍵字,表示當前節(jié)點所處層級,?這里的層級指的是?從?start?with?查詢到的節(jié)點開始往下算起,?當前屬于第幾層級驶臊。第一層是數(shù)字1挪挤,第二層數(shù)字2,依次遞增关翎。

2扛门、CONNECT_BY_ROOT方法,能夠獲取第一層集結(jié)點結(jié)果集中的任意字段的值纵寝;例CONNECT_BY_ROOT(字段名)论寨。

3、nocycle關(guān)鍵字,?有時候數(shù)據(jù)本身?不合理會導致出現(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?表示當前節(jié)點是否是葉子節(jié)點

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

另外一點:?如果在查詢語句中?Select?ID,?PID,?DSC,?connect_by_isleaf?isLeaf,?LEVEL?-?1?LEVEL?這種查詢方式的話,?在?WHERE?判斷條件中,?只需要判斷?LEVEL?=?1,?就可以取出?當前查詢節(jié)點的?子節(jié)點(由于LEVEL?也是?偽列,?需要用子查詢的方式);

(五)start?with?,connect?by?prior其他變形

上面講的用START?WITH?指定樹的根,然后用CONNECT?BY指定遞歸條件.是最簡單的也是最常用的形式.但實際上還有些變形.

1.START?WITH?可以省略

比如

SELECT?son?FROM?tree

CONNECT?BY?PRIOR?son?=?father;

此時不指定樹的根的話,就默認把Tree整個表中的數(shù)據(jù)從頭到尾遍歷一次,每一個數(shù)據(jù)做一次根,然后遍歷樹中其他節(jié)點信息.

在這個例子中,上面的SQL等價于

SELECT?son?FROM?tree

START?WITH?father?IN?(爺爺,爸爸,兒子,孫子NB,孫子SB)

CONNECT?BY?PRIOR?son?=?father;

那查詢到的結(jié)果如下,有很多重復信息的

爸爸,兒子,孫子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)鍵字

我們知道標準的樹結(jié)構(gòu)中是不會有環(huán)的,但表中的樹形結(jié)構(gòu)不是標準的,有可能導致環(huán)的出現(xiàn)

比如

?????????????---------孫子SB

?????????????|??????????????^

?????????????|??????????????|

爺爺?-->?爸爸?-->?兒子?-->孫子NB

哎在這里想用線條整個箭頭出來真他媽麻煩啊.我又有點懶不想用其他畫圖工具啥的啊.反正假設(shè)兒子的兒子是孫子SB?,而孫子SB的兒子是爸爸.這樣就形成一個環(huán)了.

當然在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;

這是開頭的寫法,但實際上也可以寫成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')),

--評論回復數(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)?評論回復數(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;

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末沮明,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子窍奋,更是在濱河造成了極大的恐慌,老刑警劉巖酱畅,帶你破解...
    沈念sama閱讀 223,002評論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件琳袄,死亡現(xiàn)場離奇詭異,居然都是意外死亡纺酸,警方通過查閱死者的電腦和手機窖逗,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評論 3 400
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來餐蔬,“玉大人碎紊,你說我怎么就攤上這事佑附。” “怎么了仗考?”我有些...
    開封第一講書人閱讀 169,787評論 0 365
  • 文/不壞的土叔 我叫張陵音同,是天一觀的道長。 經(jīng)常有香客問我秃嗜,道長权均,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 60,237評論 1 300
  • 正文 為了忘掉前任锅锨,我火速辦了婚禮叽赊,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘必搞。我一直安慰自己必指,他們只是感情好,可當我...
    茶點故事閱讀 69,237評論 6 398
  • 文/花漫 我一把揭開白布恕洲。 她就那樣靜靜地躺著取劫,像睡著了一般。 火紅的嫁衣襯著肌膚如雪研侣。 梳的紋絲不亂的頭發(fā)上谱邪,一...
    開封第一講書人閱讀 52,821評論 1 314
  • 那天,我揣著相機與錄音庶诡,去河邊找鬼惦银。 笑死,一個胖子當著我的面吹牛末誓,可吹牛的內(nèi)容都是我干的扯俱。 我是一名探鬼主播,決...
    沈念sama閱讀 41,236評論 3 424
  • 文/蒼蘭香墨 我猛地睜開眼喇澡,長吁一口氣:“原來是場噩夢啊……” “哼迅栅!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起晴玖,我...
    開封第一講書人閱讀 40,196評論 0 277
  • 序言:老撾萬榮一對情侶失蹤读存,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后呕屎,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體让簿,經(jīng)...
    沈念sama閱讀 46,716評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,794評論 3 343
  • 正文 我和宋清朗相戀三年秀睛,在試婚紗的時候發(fā)現(xiàn)自己被綠了尔当。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,928評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡蹂安,死狀恐怖椭迎,靈堂內(nèi)的尸體忽然破棺而出锐帜,到底是詐尸還是另有隱情,我是刑警寧澤畜号,帶...
    沈念sama閱讀 36,583評論 5 351
  • 正文 年R本政府宣布缴阎,位于F島的核電站,受9級特大地震影響弄兜,放射性物質(zhì)發(fā)生泄漏药蜻。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 42,264評論 3 336
  • 文/蒙蒙 一替饿、第九天 我趴在偏房一處隱蔽的房頂上張望语泽。 院中可真熱鬧,春花似錦视卢、人聲如沸踱卵。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,755評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽惋砂。三九已至,卻和暖如春绳锅,著一層夾襖步出監(jiān)牢的瞬間西饵,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,869評論 1 274
  • 我被黑心中介騙來泰國打工鳞芙, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留眷柔,地道東北人。 一個月前我還...
    沈念sama閱讀 49,378評論 3 379
  • 正文 我出身青樓原朝,卻偏偏與公主長得像驯嘱,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子喳坠,可洞房花燭夜當晚...
    茶點故事閱讀 45,937評論 2 361

推薦閱讀更多精彩內(nèi)容

  • 最近忙的不行本地安裝nacos ,它既是注冊中心,又是配置中心 默認端口8848(珠穆朗瑪峰的高度)下載安裝自行百...
    寂寞旅行閱讀 987評論 0 0
  • If you’re unhappy at work, I have to ask: Why do you expe...
    春生閣閱讀 319評論 0 1
  • 新的舊作品鞠评,黑白漫畫《庸人之路》。本想著畫的更厲害的時候再完成它壕鹉,仔細想想剃幌,不畫根本沒辦法進步,御板,全部101p锥忿。應(yīng)...
    八頭充電器閱讀 648評論 2 3
  • 我是黑夜里大雨紛飛的人啊 1 “又到一年六月,有人笑有人哭怠肋,有人歡樂有人憂愁,有人驚喜有人失落淹朋,有的覺得收獲滿滿有...
    陌忘宇閱讀 8,547評論 28 53
  • 人工智能是什么笙各?什么是人工智能钉答?人工智能是未來發(fā)展的必然趨勢嗎?以后人工智能技術(shù)真的能達到電影里機器人的智能水平嗎...
    ZLLZ閱讀 3,790評論 0 5