Oracle遞迴查詢start with connect by prior
一、基本語法
connect by遞迴查詢基本語法是:
select 1 from 表格 start with ... connect by prior id = pId
start with:表示以什麼為根節點,不加限制可以寫1=1,要以id為123的節點為根節點,就寫為start with id =123
connect by:connect by是必須的,start with有些情況是可以省略的,或者直接start with 1=1不加限制
prior:prior關鍵字可以放在等號的前面,也可以放在等號的後面,表示的意義是不一樣的,比如 prior id = pid,就表示id就是這條記錄的根節點了
二、業務場景
舉個例子,寫條SQL:
t_user(使用者表),t_unit_info(單位表),意思是以單位編碼為"15803"的單位為根節點,查找出其單位及其子單位的使用者資訊
select us.user_code, us.user_name from t_user us where exists (select 1 fromt_unit_info uinfo where us.unit_code = uinfo.unit_code start with uinfo.unit_code = '15803' connect by prior uinfo.unit_code = uinfo.para_unit_code)
然後將prior換一下位置,發現只能查出單位編碼為"15803"對應的單位,不能查子級單位
select us.user_code, us.user_name from t_user us where exists (select 1 fromt_unit_info uinfo where us.unit_code = uinfo.unit_code start with uinfo.unit_code = '15803' connect byuinfo.unit_code = prior uinfo.para_unit_code)
Oracle 遞迴查詢start with connect by prior的用法和知識不僅僅這些,本部落格只是簡單記錄一下我所遇到的,比較詳細的在Linux公社,https://www.linuxidc.com/Linux/2014-06/102687.htm
更多Oracle相關資訊見 Oracle 專題頁面 https://www.linuxidc.com/topicnews.aspx?tid=12
Linux公社的RSS地址 :https://www.linuxidc.com/rssFeed.aspx
本文永久更新連結地址:https://www.linuxidc.com/Linux/2019-03/157225.htm