oracle connect by start with

来源：互联网发布：linux安全加固方案编辑：程序博客网时间：2024/05/10 20:37

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;

参考

http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html

0 0