Oracle-递归查询

Oracle--递归查询
select * from TBL_TEST start with id=1 connect by prior id = pid

?从末梢往树ROOT递归

select * from TBL_TEST start with id=5 connect by prior pid = id

可通过level 关键字查询所在层次?

select a.*,level from TBL_TESTstart with id=1 connect by prior id = pid?

通过子节点获得顶节点

select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid  

下拉中用数据源取年份时，可用如下方式写：
select * from
(select rownum,to_char(add_months(sysdate, 4), 'yyyy') - rownum
from dual connect by rownum<5);