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;
OracleÁÙʱ±í¿ÉÒÔ˵ÊÇÌá¸ßÊý¾Ý¿â´¦ÀíÐÔÄܵĺ÷½·¨£¬ÔÚûÓбØÒª´æ´¢Ê±£¬Ö»´æ´¢ÔÚOracleÁÙʱ±í¿Õ¼äÖС£Ï£Íû±¾ÎÄÄܶԴó¼ÒÓÐËù°ïÖú¡£
1 ¡¢Ç°ÑÔ
Ä¿Ç°ËùÓÐʹÓà Oracle ×÷ΪÊý¾Ý¿âÖ§³Åƽ̨µÄÓ¦Ó㬴󲿷ÖÊý¾ÝÁ¿±È½ÏÅÓ´óµÄϵͳ£¬¼´±íµÄÊý¾ÝÁ¿Ò»°ãÇé¿ö϶¼ÊÇÔÚ°ÙÍò¼¶ÒÔÉϵÄÊý¾ÝÁ¿¡£
µ±È»ÔÚ Oracle Öд´½¨·ÖÇøÊÇÒ»ÖÖ²»´íµÄÑ¡Ôñ£¬µ« ......