oracle之connect by用法

oracle connect by用法

场景一上下级关系
表：sun_test

向下递归遍历

select * from sun_test connect by mgr=prior emp start with emp='003';

从003开始查，遍历003所有下级员工直到没有为止
向上递归遍历

select * from sun_test connect by prior mgr=emp start with emp='003'

从003开始查，遍历003所有上级直到没有为止
联查结果

select * from sun_test connect by prior mgr=emp start with emp='003'
union
select * from sun_test connect by mgr=prior emp start with emp='003'

场景二未设计上下级关系

select rownum c1 from dual connect by  rownum <= 10

可见connect by的功能主要是根据一个条件来遍历所有满足条件的元素