Oracle Connect ByÓ÷¨
oracleÖеÄselectÓï¾ä¿ÉÒÔÓÃSTART WITH...CONNECT BY PRIOR×Ó¾äʵÏֵݹé²éѯ£¬connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;
<Conditional-1>£º¹ýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£
<Conditional-2>£º²éѯ½á¹ûÖØÆðʼ¸ù½áµãµÄÏÞ¶¨Ìõ¼þ¡£
<Conditional-3>£ºÁ¬½ÓÌõ¼þ
Êý¾Ý¿â±í½á¹¹ÈçÏ£º
create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);
insert into t2(root_id,id,name,description) values(0,1,'a','aaa');
insert into t2(root_id,id,name,description) values(1,2,'a1','aaa1');
insert into t2(root_id,id,name,description) values(1,3,'a2','aaa2');
insert into t2(root_id,id,name,description) values(0,4,'b','bbb');
insert into t2(root_id,id,name,description) values(4,5,'b1','bbb1');
insert into t2(root_id,id,name,description) values(4,6,'b2','bbb2');
»ñÈ¡ÍêÕûÊ÷£º
select * from t2 start with root_id = 0 connect by prior id = root_id;
»ñÈ¡Ìض¨×ÓÊ÷£º
select * from t2 start with id = 1 connect by prior id = root_id;
select * from t2 start with id = 4 connect by prior id = root_id;
Èç¹ûconnect by priorÖеÄprior±»Ê¡ÂÔ£¬Ôò²éѯ½«²»½øÐÐÉî²ãµÝ¹é¡£
È磺
select * from t2 start with root_id = 0 connect by id = root_id;
select * from t2 start with id = 1 connect by id = root_id;
ÖйúµçÁ¦ÐÐҵĿǰÕýÔÚ¾Àúמ޴óµÄ±ä»¯£º´Ó¹ú¼Ò°ìµçµ½ÆóÒµ°ìµç£¬ÐγÉÁ˶à¼Ò¾ºÕù°ìµçµÄ¸ñ¾Ö£»³§Íø·Ö¿ª¡¢¾º¼ÛÉÏÍø£¬´Ó¼Æ»®µ½Êг¡×ª¹ì£¬ÒýÈë¶àÖÖ¾¼Ã³É·Ö£»´ÓµØÓòÐÔ·ÖÉ¢¹©µçÏò´óµçÍø·¢Õ¹£»´ÓÕþÆó²»·Öµ½ÕþÆó·Ö¿ª¡£ÕâЩ±ä»¯£¬Ê¹ÖйúµÄµçÁ¦ÐÐÒµ¾ÊÜ×ÅÇ°ËùδÓеÄѹÁ¦¡£
Ò»·½Ã棬2003Äê¹úÄڵķ¢µçÆóÒµÖØиÄ×é£ ......