Oracle涓璼tart with...connect by prior瀛愬彞鐢ㄦ硶
connect by 鏄粨鏋勫寲鏌ヨ涓敤鍒扮殑锛屽叾鍩烘湰璇硶鏄細
select ... from tablename start with 鏉′欢1
connect by 鏉′欢2
where 鏉′欢3;

渚嬶細
Start with...Connect By瀛愬彞閫掑綊鏌ヨ涓€鑸敤浜庝竴涓〃缁存姢鏍戝舰缁撴瀯鐨勫簲鐢ㄣ€?
鍒涘缓绀轰緥琛細
CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER  DEFAULT 0
);

鎻掑叆娴嬭瘯鏁版嵁锛?
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

浠嶳oot寰€鏍戞湯姊㈤€掑綊
select * from TBL_TEST
start with id=1
connect by prior id = pid

浠庢湯姊㈠線鏍慠OOT閫掑綊
select * from TBL_TEST
start with id=5
connect by prior pid = id


绠€鍗曡鏉ユ槸灏嗕竴涓爲鐘剁粨鏋勫瓨鍌ㄥ湪涓€寮犺〃閲岋紝姣斿涓€涓〃涓瓨鍦ㄤ袱涓瓧娈?
org_id,parent_id閭ｄ箞閫氳繃琛ㄧず姣忎竴鏉¤褰曠殑parent鏄皝锛屽氨鍙互褰㈡垚涓€涓爲鐘剁粨鏋勩€?
鐢ㄤ笂杩拌娉曠殑鏌ヨ鍙互鍙栧緱杩欐５鏍戠殑鎵€鏈夎褰曘€?
鍏朵腑锛?
鏉′欢1 鏄牴缁撶偣鐨勯檺瀹氳鍙ワ紝褰撶劧鍙互鏀惧闄愬畾鏉′欢锛屼互鍙栧緱澶氫釜鏍圭粨鐐癸紝瀹為檯灏辨槸澶氭５鏍戙€?
鏉′欢2 鏄繛鎺ユ潯浠讹紝鍏朵腑鐢≒RIOR琛ㄧず涓婁竴鏉¤褰曪紝姣斿 CONNECT BY PRIOR org_id = parent_id灏辨槸璇翠笂涓€鏉¤褰曠殑org_id 鏄湰鏉¤褰曠殑parent_id锛屽嵆鏈褰曠殑鐖朵翰鏄笂涓€鏉¤褰曘€?
鏉′欢3 鏄繃婊ゆ潯浠讹紝鐢ㄤ簬瀵硅繑鍥炵殑鎵€鏈夎褰曡繘琛岃繃婊ゃ€?



绠€鍗曚粙缁嶅涓嬶細

鏃╂壂鎻忔爲缁撴瀯琛ㄦ椂锛岄渶瑕佷緷姝よ闂爲缁撴瀯鐨勬瘡涓妭鐐癸紝涓€涓妭鐐瑰彧鑳借闂竴娆★紝鍏惰闂殑姝ラ濡備笅锛?
绗竴姝ワ細浠庢牴鑺傜偣寮€濮嬶紱
绗簩姝ワ細璁块棶璇ヨ妭鐐癸紱
绗笁姝ワ細鍒ゆ柇璇ヨ妭鐐规湁鏃犳湭琚闂殑瀛愯妭鐐癸紝鑻ユ湁锛屽垯杞悜瀹冩渶宸︿晶鐨勬湭琚闂殑瀛愯妭锛屽苟鎵ц绗簩姝ワ紝鍚﹀垯鎵ц绗洓姝ワ紱
绗洓姝ワ細鑻ヨ鑺傜偣涓烘牴鑺傜偣锛屽垯璁块棶瀹屾瘯锛屽惁鍒欐墽琛岀浜旀锛?
绗簲姝ワ細杩斿洖鍒拌鑺傜偣鐨勭埗鑺傜偣锛屽苟鎵ц绗笁姝ラ銆?

鎬讳箣锛氭壂鎻忔暣涓爲缁撴瀯鐨勮繃绋嬩篃鍗虫槸涓簭閬嶅巻鏍戠殑杩囩▼銆?


1锛?鏍戠粨鏋勭殑鎻忚堪
鏍戠粨鏋勭殑鏁版嵁瀛樻斁鍦ㄨ〃涓紝鏁版嵁涔嬮棿鐨勫眰娆″叧绯诲嵆鐖跺瓙鍏崇郴锛岄€氳繃琛ㄤ腑鐨勫垪涓庡垪闂寸殑鍏崇郴鏉ユ弿杩帮紝濡侲MP琛ㄤ腑鐨凟MPNO鍜孧GR銆侲MPNO琛ㄧず璇ラ泧鍛樼殑缂栧彿锛孧GR琛ㄧず棰嗗璇ラ泧鍛樼殑浜虹殑缂栧彿锛屽嵆瀛愯妭鐐圭殑MGR鍊肩瓑浜庣埗鑺傜偣鐨凟MPNO鍊笺€傚湪琛ㄧ殑姣忎竴琛屼腑閮芥湁涓€涓〃绀虹埗鑺傜偣鐨凪GR锛堥櫎鏍硅妭鐐瑰锛夛紝閫氳繃姣忎釜鑺傜偣鐨勭埗鑺傜偣锛屽氨鍙互纭畾鏁翠釜鏍戠粨鏋勩€?
鍦⊿ELECT鍛戒护涓娇鐢–ONNECT BY 鍜岃敿START WITH 瀛愬彞鍙互鏌ヨ琛ㄤ腑鐨勬爲鍨嬬粨鏋勫叧绯汇€傚叾鍛戒护鏍煎紡濡備笅锛?
SELECT 銆傘€傘€?
CONNECT BY {PRIOR 鍒楀悕1=鍒楀悕2|鍒楀悕1=PRIOR 鍒楀悕2}
[START WITH]锛?
鍏朵腑锛欳ONNECT BY瀛愬彞璇存槑姣忚鏁版嵁灏嗘槸鎸夊眰娆￠『搴忔绱紝骞惰瀹氬皢琛ㄤ腑灏嗚〃涓殑鏁版嵁杩炲叆鏍戝瀷缁撴瀯鐨勫叧绯讳腑銆侾RIORY杩愮畻绗﹀繀椤绘斁缃湪杩炴帴鍏崇郴鐨勪袱鍒椾腑鏌愪竴涓殑鍓嶉潰銆傚浜庤妭鐐归棿鐨勭埗瀛愬叧绯伙紝PRIOR杩愮畻绗﹀湪涓€渚ц〃绀虹埗鑺傜偣锛屽湪鍙︿竴渚ц〃绀哄瓙鑺傜偣锛屼粠鑰岀‘瀹氭煡鎵炬爲缁撴瀯鏄殑椤哄簭鏄嚜椤跺悜涓嬭繕鏄嚜搴曞悜涓娿€傚湪杩炴帴鍏崇郴涓紝闄や簡鍙互浣跨敤鍒楀悕澶栵紝杩樺厑璁镐娇鐢ㄥ垪琛ㄨ揪寮忋€係TART WITH 瀛愬彞涓哄彲閫夐」锛岀敤鏉ユ爣璇嗗摢涓妭鐐逛綔涓烘煡鎵炬爲鍨嬬粨鏋勭殑鏍硅妭鐐广€傝嫢璇ュ瓙鍙ヨ鐪佺暐锛屽垯琛ㄧず鎵€鏈夋弧瓒虫煡璇㈡潯浠剁殑琛屼綔涓烘牴鑺傜偣銆?

START WITH锛?涓嶄絾鍙互鎸囧畾涓€涓牴鑺傜偣锛岃繕鍙互鎸囧畾澶氫釜鏍硅妭鐐广€?
2锛?鍏充簬PRIOR
杩愮畻绗RIOR琚斁缃簬绛夊彿鍓嶅悗鐨勪綅缃紝鍐冲畾鐫€鏌ヨ鏃剁殑妫€绱㈤『搴忋€?
PRIOR琚疆浜嶤ONNECT BY瀛愬彞涓瓑鍙风殑鍓嶉潰鏃讹紝鍒欏己鍒朵粠鏍硅妭鐐瑰埌鍙惰妭鐐圭殑椤哄簭妫€绱紝鍗崇敱鐖惰妭鐐瑰悜瀛愯妭鐐规柟鍚戦€氳繃鏍戠粨鏋勶紝鎴戜滑绉颁箣涓鸿嚜椤跺悜涓嬬殑鏂瑰紡銆傚锛?
CONNECT BY PRIOR EMPNO=MGR
PIROR杩愮畻绗﹁缃簬CONNECT BY 瀛愬彞涓瓑鍙风殑鍚庨潰鏃讹紝鍒欏己鍒朵粠鍙惰妭鐐瑰埌鏍硅妭鐐圭殑椤哄簭妫€绱紝鍗崇敱瀛愯妭鐐瑰悜鐖惰妭鐐规柟鍚戦€氳繃鏍戠粨鏋勶紝鎴戜滑绉颁箣涓鸿嚜搴曞悜涓婄殑鏂瑰紡銆備緥濡傦細
CONNECT BY EMPNO=PRIOR MGR
鍦ㄨ繖绉嶆柟寮忎腑涔熷簲鎸囧畾涓€涓紑濮嬬殑鑺傜偣銆?
3锛?瀹氫箟鏌ユ壘璧峰鑺傜偣
鍦ㄨ嚜椤跺悜涓嬫煡璇㈡爲缁撴瀯鏃讹紝涓嶄絾鍙互浠庢牴鑺傜偣寮€濮嬶紝杩樺彲浠ュ畾涔変换浣曡妭鐐逛负璧峰鑺傜偣锛屼互姝ゅ紑濮嬪悜涓嬫煡鎵俱€傝繖鏍锋煡鎵剧殑缁撴灉灏辨槸浠ヨ鑺傜偣涓哄紑濮嬬殑缁撴瀯鏍戠殑涓€鏋濄€?

4锛庝娇鐢↙EVEL
鍦ㄥ叿鏈夋爲缁撴瀯鐨勮〃涓紝姣忎竴琛屾暟鎹兘鏄爲缁撴瀯涓殑涓€涓妭鐐癸紝鐢变簬鑺傜偣鎵€澶勭殑灞傛浣嶇疆涓嶅悓锛屾墍浠ユ瘡琛岃褰曢兘鍙互鏈変竴涓眰鍙枫€傚眰鍙锋牴鎹妭鐐逛笌鏍硅妭鐐圭殑璺濈纭畾銆備笉璁轰粠鍝釜鑺傜偣寮€濮嬶紝璇ヨ捣濮嬫牴鑺傜偣鐨勫眰鍙峰缁堜负1锛屾牴鑺傜偣鐨勫瓙鑺傜偣涓?锛?渚濇绫绘帹銆傚浘1.2灏辫〃绀轰簡鏍戠粨鏋勭殑灞傛銆?

5锛庤妭鐐瑰拰鍒嗘敮鐨勮鍓?
鍦ㄥ鏍戠粨鏋勮繘琛屾煡璇㈡椂锛屽彲浠ュ幓鎺夎〃涓殑鏌愪簺琛岋紝涔熷彲浠ュ壀鎺夋爲涓殑涓€涓垎鏀紝浣跨敤WHERE瀛愬彞鏉ラ檺瀹氭爲鍨嬬粨鏋勪腑鐨勫崟涓妭鐐癸紝浠ュ幓鎺夋爲涓殑鍗曚釜鑺傜偣锛屼絾瀹冨嵈涓嶅奖鍝嶅叾鍚庝唬鑺傜偣锛堣嚜椤跺悜涓嬫绱㈡椂锛夋垨鍓嶈緢鑺傜偣锛堣嚜搴曞悜椤舵绱㈡椂锛夈€?
6锛庢帓搴忔樉绀?
璞″湪鍏跺畠鏌ヨ涓竴鏍凤紝鍦ㄦ爲缁撴瀯鏌ヨ涓篃鍙互浣跨敤ORDER BY 瀛愬彞锛屾敼鍙樻煡璇㈢粨鏋滅殑鏄剧ず椤哄簭锛岃€屼笉蹇呮寜鐓ч亶鍘嗘爲缁撴瀯鐨勯『搴忋€?