oracleµÝ¹é²éѯ

oraÎÊ´ðcleµÝ¹é²éѯ
OracleÖÐstartbyprior×Ó¾äÓ÷¨
connectbyÊǽṹ»¯²éѯÖж¨½ñ¼Ì¿éÁ³ÕßÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
selectfromtablenamestartwithÌõ¼þ1
connect²Å¿ÕbyÌõ¼þ2
whereÌõÕ¾Ó¦¾³Å©²è¼þÀïÑϺú¼þ3;


Àý£º
seʷסµ×ЧӪδÀ©¾ö¼°lect*fromtable
startwithorgÅàÔÚÒ»¸ßÎÒÉÜ´ðÓ³ÖÕ¼¾_id='h**Bh**qfWGWPy'
connectbypriororg_id=ָŞ©ÃÅÄïϯÑî¸Ë¾ÝÏì¹¥parent_id;


¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö¿ÚÏÔ¼ÃÁ¦ÂýÊÓ½ôÏÈ×Ö¶Î:
org_id,parent_idÄÇôͨ¹ý±íʾÿһÌõ¼Ç¼µÄparentÊÇË­£¬¾Í¿ÉÒÔÐαêÊÆÖæËØ´å¿éÔ¬Çàϣʳ³ÉÒ»¸öÊ÷×´½á¹¹¡£
Óòè¾ÝÐ˼ûÏÈÇжÔÉÕÑÔ³¤ÐËÉÏÊöÓï·¨µÄ²éѯ¿ÉÒÔÈ¡µÃÕâ¿ÃÊ÷µÄËùÓмǼ¡£
ÆäÖУº


Ìõ¼þ1ÊǸù½áµãµÄÏÞ¶¨Óï¾ä£¬µ±È»¿ÉÒÔ·Å¿íÏÞ¶¨Ìõ¼þ£¬ÒÔÈ¡µÃ¶à¸ö¸ù½áµã£¬Êµ¼Ê¾ÍÊǶà¿ÃÊ÷¡£
Ìõ¼þ2ÊÇÁ¬½ÓÌõ¼þ£¬ÆäÖÐÓÃPRIOR±íʾÉÏÒ»Ìõ¼Ç¼£¬±ÈÈçCONNECTBYPRIORorg_id=parent_id¾ÍÊÇ˵ÉÏÒ»Ìõ¼Ç¼µÄorg_idÊDZ¾Ìõ¼Ç¼µÄparent_id£¬¼´±¾¼Ç¼µÄ¸¸Ç×ÊÇÉÏÒ»Ìõ¼Ç¼¡£
Ìõ¼þ3ÊýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£






¼òµ¥½éÉÜÈçÏ£º


ÔçɨÃèÊ÷½á¹¹±íʱ£¬ÐèÒªÒÀ´Ë·ÃÎÊÊ÷½á¹¹µÄÿ¸ö½Úµã£¬Ò»¸ö½ÚµãÖ»ÄÜ·ÃÎÊÒ»´Î£¬Æä·ÃÎʵIJ½ÖèÈçÏ£º
µÚÒ»²½£º´Ó¸ù½Úµã¿ªÊ¼£»
µÚ¶þ²½£º·ÃÎʸýڵ㣻
µÚÈý²½£ºÅжϸýڵãÓÐÎÞδ±»·ÃÎʵÄ×ӽڵ㣬ÈôÓУ¬ÔòתÏòËü×î×ó²àµÄδ±»·ÃÎʵÄ×Ó½Ú£¬²¢Ö´Ðеڶþ²½£¬·ñÔòÖ´ÐеÚËIJ½£»
µÚËIJ½£ºÈô¸Ã½ÚµãΪ¸ù½Úµã£¬Ôò·ÃÎÊÍê±Ï£¬·ñÔòÖ´ÐеÚÎå²½£»
µÚÎå²½£º·µ»Øµ½¸Ã½ÚµãµÄ¸¸½Úµã£¬²¢Ö´ÐеÚÈý²½Öè¡£


×ÜÖ®£ºÉ¨ÃèÕû¸öÊ÷½á¹¹µÄ¹ý³ÌÒ²¼´ÊÇÖÐÐò±éÀúÊ÷µÄ¹ý³Ì¡£






1£®Ê÷½á¹¹µÄÃèÊö
Ê÷½á¹¹µÄ**´æ·ÅÔÚ±íÖУ¬**Ö®¼äµÄ²ã´Î¹Øϵ¼´¸¸×Ó¹Øϵ£¬Í¨¹ý±íÖеÄÁÐÓëÁмäµÄ¹ØϵÀ´ÃèÊö£¬ÈçEMP±íÖеÄEMPNOºÍMGR¡£EMPNO±íʾ¸Ã¹ÍÔ±µÄ±àºÅ£¬MGR±íʾÁìµ¼¸Ã¹ÍÔ±µÄÈ˵ıàºÅ£¬¼´×Ó½ÚµãµÄMGRÖµµÈÓÚ¸¸½ÚµãµÄEMPNOÖµ¡£ÔÚ±íµÄÿÂèÖÆ»ðÐø¹ÙÑù°å¾¿Ô½½¨Ò»ÐÐÖж¼ÓÐÒ»¸ö±íʾ¸¸½ÚµãµÄMGR£¨³ý¸ù½ÚµãÍ⣩£¬Í¨¹ýÿ¸ö½ÚµãµÄ¸¸½Úµã£¬¾Í¿ÉÒÔÈ·¶¨Õû¸öÊ÷½á¹¹¡£
ÔÚ**LECTÃüÁîÖÐʹÓÃCONNECTBYºÍ°ªSTARTWIÕ÷µÈÊö¿¹¹Ì¼ÜTh**×Ó¾ä¿ÉÒÔ²éѯ±íÖмûµÄÊ÷Ðͽṹ¹Øϵ¡£ÆäÃüÁî¸ñʽÈçÏ£º
**LECT¡£¡£¡£
CONNECTBY{PRIORÁÐÃû1=ÁÐÃû2|ÁÐÃû1=PRIORÁÑÃû2}
[STARTWITh**]£»
ÆäÖУºCONNECTBY×Ó¾ä˵Ã÷ÿÐÐ**½«ÊÇ°´²ã´Î˳Ðò¼ìË÷£¬²¢¹æ¶¨½«±íÖеÄ**Á¬ÈëÊ÷ÐͽṹµÄ¹ØϵÖС£PRIORYÔËËã·û±ØÐë·ÅÖÃÔÚÁ¬½Ó¹ØϵµÄÁ½ÁÐÖÐijһ¸öµÄÇ°Ãæ¡£¶ÔÓڽڵ㳾­²Å´ð¼äµÄ¸¸×Ó¹Øϵ£¬PRIORÔËËã·ûÔÚÒ»²à°¸Áã±íʾ¸¸½Úµã£¬ÔÚÁíÒ»²à±íʾ×Ó½Ú¾¿ÑîÑù×é»ñ»¬Á£»ùµã£¬´Ó¶øÈ·¶¨²éÕÒÊ÷½á¹¹ÊǵÄ˳ÐòÊÇ×Ô¶¥ÏòÏ»¹ÊÇ×Ôµ×ÏòÉÏ¡£ÔÚÁ¬½Ó¹ØϵÖУ¬³ýÁË¿ÉÒÔʹÓÃÁÐÃûÍ⣬ÔÆÕ⻹ÔÊÐíʹÓÃÁбí´ïʽ¡£STARTWITh**×Ó¾äΪ¿ÉÑ¡ÏÓÃÀ´±êʶÄĸö½Úµã×÷Ϊ²éÕÒÊ÷ÐͽṹµÄ¸ù½Úµã¡£Èô¸Ã×Ӿ䱻ʡÂÔ£¬Ôò±íʾËùÓÐÂú×ã²éѯÌõ¼þµÄÐÐ×÷Ϊ¸ù½Úµã¡£


STARTWITh**£º²»µ«¿ÉÒÔÖ¸¶¨Ò»¸ö¸ù½Úµã£¬»¹¿ÉÒÔÖ¸¶¨¶à¸ö¸ù½Úµã¡£
2£®¹ØÓÚPRIOR
ÔËËã·ûPRIOR±»·ÅÖÃÓڵȺÅÇ°ºó»Øϵ¸ß»ÊÍâµÄλÖ㬾ö¶¨×ŲéѯʱµÄ¼ìË÷˳Ðò¡£
PRIOR±»ÖÃÓÚCONNECTBY×Ó¾äÖеȺŵÄÇ°Ãæʱ£¬ÔòÇ¿ÖÆ´Ó¸ù½Úµãµ½Ò¶½ÚµãµÄ˳Ðò¼ìË÷£¬¼´Óɸ¸½ÚµãÏò×ӽڵ㷽Ïòͨ¹ýÊ÷½á¹¹£¬ÎÒÃdzÆ֮Ϊ×Ô¶¥Ïòϵķ½Ê½¡£È磺
CONNECTBYPRIOREMPNO=MGR
PIRORÔËËã·û±»ÖÃÓÚCONNECTBY×Ó¾äÖеȺŵĺóÃæʱ£¬ÔòÇ¿ÖÆ´ÓÒ¶½Úµãµ½¸ù½ÚµãµÄ˳Ðò¼ìË÷£¬¼´ÓÉ×Ó½ÚµãÏò¸¸½Úµã·½Ïòͨ¹ýÊ÷½á¹¹£¬ÎÒÃdzÆ֮Ϊ×Ôµ×ÏòÉϵķ½Ê½¡£ÀýÈ磺
CONNECTBYEMPNO=PRIORMGR
ÔÚÕâÖÖ·½Ê½ÖÐÒ²Ó¦Ö¸¶¨Ò»¸ö¿ªÊ¼µÄ½Úµã¡£
3£®¶¨Òå²éÕÒÆðʼ½Úµã
ÔÚ×Ô¶¥ÏòϲéѯÊ÷½á¹¹Ê±£¬²»µ«¿ÉÒÔ´Ó¸ù½Úµã¿ªÊ¼£¬»¹¿ÉÒÔ¶¨ÒåÈκνڵãΪÆðʼ½Úµã£¬ÒÔ´Ë¿ªÊ¼ÏòϲéÕÒ¡£ÕâÑù²éÕҵĽá¹û¾ÍÊÇÒԸýڵãΪ¿ªÊ¼µÄ½á¹¹Ê÷µÄÒ»Ö¦¡£


4£®Ê¹ÓÃLEVEL
ÔÚ¾ßÓÐÊ÷½á¹¹µÄ±íÖУ¬Ã¿Ò»ÐÐ**¶¼ÊÇÊ÷½á¹¹ÖеÄÒ»¸ö½Úµã£¬ÓÉÓÚ½ÚµãËù´¦µÄ²ã´ÎλÖò»Í¬£¬ËùÒÔÿÐмǼ¶¼¿ÉÒÔÓÐÒ»¸ö²ãºÅ¡£²ãºÅ¸ù¾Ý½ÚµãÓë¸ù½ÚµãµÄ¾àÀëÈ·¶¨¡£²»ÂÛ´ÓÄĸö½Úµã¿ªÊ¼£¬¸ÃÆðʼ¸ù½ÚµãµÄ²ãºÅʼÖÕΪ1£¬¸ù½ÚµãµÄ×Ó½ÚµãΪ2£¬ÒÀ´ËÀàÍÆ¡£Í¼1.2¾Í±íʾÁËÊ÷½á¹¹µÄ²ã´Î¡£


5£®½ÚµãºÍ·ÖÖ§µÄ²Ã¼ô
ÔÚ¶ÔÊ÷½á¹¹½øÐвéѯʱ£¬¿ÉÒÔÈ¥µô±íÖеÄijЩÐУ¬Ò²¿ÉÒÔ¼ôµôÊ÷ÖеÄÒ»¸ö·ÖÖ§£¬Ê¹ÓÃWh**ERE×Ó¾äÀ´ÏÞ¶¨Ê÷ÐͽṹÖеĵ¥¸ö½Úµã£¬ÒÔÈ¥µôÊ÷Öеĵ¥¸ö½Úµã£¬µ«ËüÈ´²»Ó°ÏìÆäºó´ú½Úµã£¨×Ô¶¥ÏòϼìË÷ʱ£©»òÇ°±²½Úµã£¨×Ôµ×Ïò¶¥¼ìË÷ʱ£©¡£
6£®ÅÅÐòÏÔʾ
ÏóÔÚÆäËü²éѯÖÐÒ»Ñù£¬ÔÚÊ÷½á¹**éѯÖÐÒ²¿ÉÒÔʹÓÃORDERBY×Ӿ䣬¸Ä±ä²éѯ½á¹ûµÄÏÔʾ˳Ðò£¬¶ø²»±Ø°´ÕÕ±éÀúÊ÷½á¹¹µÄ˳Ðò¡£