oralce遞歸查詢

1.基本語法是：

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>;
//<Conditional-1>：過濾條件，用于對(duì)返回的所有記錄進(jìn)行過濾。
//<Conditional-2>：查詢結(jié)果重起始根結(jié)點(diǎn)的限定條件。
//<Conditional-3>：連接條件
//如果connect by prior中的prior被省略，則查詢將不進(jìn)行深層遞歸。

2.向上查詢當(dāng)前菜單及所有上級(jí)菜單

select t.* from s_menu t start with t.id='510' connect by prior t.fid = t.id

3.向上查詢所有上級(jí)菜單(不包含當(dāng)前菜單)

select t.* from s_menu t start with t.id='510' connect by prior t.fid = t.id

4.向下查詢當(dāng)前菜單及所有下級(jí)菜單

select t.* from s_menu t start with t.id='001' connect by prior t.id = t.fid 

5.向下查詢所有下級(jí)菜單(不包含當(dāng)前菜單)

select t.* from s_menu t start with t.id='001' connect by prior t.id = t.fid

6.查詢遞歸路徑

select t.id,t.name,fid,substr(sys_connect_by_path(NAME,'->'),3) menu_path
from s_menu t start with t.name = '系統(tǒng)功能' connect by prior t.id = t.fid order by t.id

7.分層次顯示

select t.id,lpad('|-',(level-1)*4,'|-')||lpad('『',2)||t.name||rpad('』',2) as newname
from s_menu t connect by prior t.id=t.fid start with t.id='-1'

總結(jié)

向上遞歸和向下遞歸的關(guān)鍵就是最后一個(gè)條件，父ID等于ID則向上遞歸，反之向下遞歸。

參考：http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html