1 select a.accountcodeid, a.accountcode, a.accountcodename, 2 SYS_CONNECT_BY_PATH(a.accountcodename, '\') as fullname, a.parentcodeid, level 3 from fm_accountcode a 4 where a.accountcodeid >= 10000 5 start with a.parentcodeid is null 6 connect by prior a.accountcodeid = a.parentcodeid; 7 8 select a.accountcodeid, a.accountcode, a.accountcodename, 9 SYS_CONNECT_BY_PATH(a.accountcodename, '\') as fullname, 10 a.parentcodeid 11 from fm_accountcode a 12 where a.accountcodeid > 10000 13 and connect_by_isleaf = 1 14 start with a.parentcodeid is null 15 connect by prior a.accountcodeid = a.parentcodeid;
oracle 中遞歸查詢,對樹型結構數據相當有用。
二者區別:上,所有數據,包含結構;下,所有末節點,包含結構,具體參見Oracle Document 11g。