DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)
²¿ÃÅid ¸¸²¿ÃÅid(ËùÊô²¿ÃÅid) ²¿ÃÅÃû³Æ

ͨ¹ý×Ó½ÚµãÏò¸ù½Úµã×·Ë·.

Sql´úÂë
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid   
 select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid  

ͨ¹ý¸ù½Úµã±éÀú×Ó½Úµã.

Sql´úÂë
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid   
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid  

¿Éͨ¹ýlevel ¹Ø¼ü×Ö²éѯËùÔÚ²ã´Î.

Sql´úÂë
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid   
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

Ôٴθ´Ï°Ò»ÏÂ:start with ...connect by µÄÓ÷¨£¬ start with ºóÃæËù¸úµÄ¾ÍÊǾÍÊǵݹéµÄÖÖ×Ó¡£

µÝ¹éµÄÖÖ×ÓÒ²¾ÍÊǵݹ鿪ʼµÄµØ·½ connect by ºóÃæµÄ"prior" Èç¹ûȱʡ£ºÔòÖ»Äܲéѯµ½·ûºÏÌõ¼þµÄÆðʼÐУ¬²¢²»½øÐеݹé²éѯ£»

connect by prior ºóÃæËù·ÅµÄ×Ö¶ÎÊÇÓйØϵµÄ£¬ËüÖ¸Ã÷Á˲éѯµÄ·½Ïò¡£

Á·Ï°£º ͨ¹ý×Ó½Úµã»ñµÃ¶¥½Úµã

Sql´úÂë

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