ORACLE树查询 start with ... connect by prior...

select *  from t_menu t start with t.pid is null connect by prior t.id = t.pid order SIBLINGS by t.levelno;

 

t_menu: id ，pid(父菜单id),levelno(菜单显示级别);

 

语法：START WITH condition CONNECT BY NOCYCLE condition ORDER SIBLINGS BY

 

START WITH 后面的语句是定义递归查询开始的条件 pid is null 查找根菜单 

 

CONNECT BY 后面的语句是定义父与子之间关系 id=pid  

 

若prior缺省：则只能查询到符合条件的起始行，并不进行递归查询

 

ORDER SIBLINGS BY 保持阶层的顺序 在递归查询结果的基础上安装菜单显示级别排序。

 

NOCYCLE  ORA-01436: CONNECT BY loop in user data 查询陷入死循环时 加入NOCYCLE则可用查找到数据