oracle connect by start with

select * from tb_menu m start with m.id=2 connect by prior m.parent= m.id;

找2的父節點，

select * from tb_menu m connect by prior  m.id=  m.parent start with m.id=2;

找2的子節點

生成不間斷的列

select rownum from dual connect by level < 10;

關于prior的了解，prior後面的字段就是父行，另外的字段來比對父行，比如上面的第一個sql，m.paraent是父行，從id=2的子行開始，向上層次查找父記錄中id字段是2的父行，第二個例子，id是父行，從id=2開始的父行開始，查找子行中parent=2的記錄

對同層的記錄進行排序

select employee_id,level,last_name,first_name from employees start with manager_id is null connect by manager_id= prior employee_id order siblings by last_name,first_name;

裁剪

不想看到last_name為‘B'及它的下級

select employee_id,level,last_name,first_name from employees start with manager_id is null connect by manager_id= prior employee_id and not(last_name='B') order siblings by last_name,first_name;

僅排除’B',還要包含它的下級

select employee_id,level,last_name,first_name from employees where not(last_name='B')  start with manager_id is null connect by manager_id= prior employee_id order siblings by last_name,first_name;

在層次中生成路徑名

select '/u01/emp'||sys_connect_by_path(last_name||','||first_name,'/') gen_path from employees start with manager_id is null connect by prior employee_id = manager_id;

辨認葉子節點

select last_name,frist_name,level,connect_by_isleaf is_leaf from employees start with manager_id is null connect by prior employee_id=manager_id;

檢視節點的最高經理，中間的不顯示

select last_name,first_name,level,connect_by_root last_name,connect_by_root first_name from employees where connect_by_isleaf=1 start with manager_id is null connect by prior employee_id = manager_id;

檢查層次結構資料中的循環

select employee_id,manager_id,level,connect_by_iscycle is_cycle,last_name,first_name from employees start with last_name='kk' connect by nocycle manager_id = prior employee_id;