oracleÊ÷½á¹¹²éѯ
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ¥ËµÀ´Êǽ«Ò»¸öÊ÷×´½á¹¹´æ´¢ÔÚÒ»ÕűíÀ±ÈÈçÒ»¸ö±íÖдæÔÚÁ½¸ö×Ö¶Î:
org_id,parent_idÄÇôͨ¹ý±íʾÿһÌõ¼Ç¼µÄparentÊÇË£¬¾Í¿ÉÒÔÐγÉÒ»¸öÊ÷×´½á¹¹¡£
ÓÃÉÏÊöÓï·¨µÄ²éѯ¿ÉÒÔÈ¡µÃÕâ¿ÃÊ÷µÄËùÓмǼ¡£
ÆäÖУº
Ìõ¼þ1 ÊǸù½áµãµÄÏÞ¶¨Óï¾ä£¬µ±È»¿ÉÒÔ·Å¿íÏÞ¶¨Ìõ¼þ£¬ÒÔÈ¡µÃ¶à¸ö¸ù½áµã£¬Êµ¼Ê¾ÍÊǶà¿ÃÊ÷¡£
Ìõ¼þ2 ÊÇÁ¬½ÓÌõ¼þ£¬ÆäÖÐÓÃPRIOR±íʾÉÏÒ»Ìõ¼Ç¼£¬±ÈÈç CONNECT BY PRIOR org_id
= parent_id¾ÍÊÇ˵ÉÏÒ»Ìõ¼Ç¼µÄorg_id ÊDZ¾Ìõ¼Ç¼µÄparent_id£¬¼´±¾¼Ç¼µÄ¸¸Ç×ÊÇÉÏÒ»Ìõ¼Ç¼¡£
Ìõ¼þ3 ÊǹýÂËÌõ¼þ£¬ÓÃÓÚ¶Ô·µ»ØµÄËùÓмǼ½øÐйýÂË¡£
¼òµ¥½éÉÜÈçÏ£º
ÔçɨÃèÊ÷½á¹¹±íʱ£¬ÐèÒªÒÀ´Ë·ÃÎÊÊ÷½á¹¹µÄÿ¸ö½Úµã£¬Ò»¸ö½ÚµãÖ»ÄÜ·ÃÎÊÒ»´Î£¬Æä·ÃÎʵIJ½ÖèÈçÏ£º
µÚÒ»²½£º´Ó¸ù½Úµã¿ªÊ¼£»
µÚ¶þ²½£º·ÃÎʸýڵ㣻
µÚÈý²½£ºÅжϸýڵãÓÐÎÞδ±»·ÃÎʵÄ×ӽڵ㣬ÈôÓУ¬ÔòתÏòËü×î×ó²àµÄδ±»·ÃÎʵÄ×Ó½Ú£¬²¢Ö´Ðеڶþ²½£¬·ñÔòÖ´ÐеÚËIJ½£»
µÚËIJ½£ºÈô¸Ã½ÚµãΪ¸ù½Úµã£¬Ôò·ÃÎÊÍê±Ï£¬·ñÔòÖ´ÐеÚÎå²½£»
µÚÎå²½£º·µ»Øµ½¸Ã½ÚµãµÄ¸¸½Úµã£¬²¢Ö´ÐеÚÈý²½Öè¡£
×ÜÖ®£ºÉ¨ÃèÕû¸öÊ÷½á¹¹µÄ¹ý³ÌÒ²¼´ÊÇÖÐÐò±éÀúÊ÷µÄ¹ý³Ì¡£
1£® Ê÷½á¹¹µÄÃèÊö
Ê÷½á¹¹µÄÊý¾Ý´æ·ÅÔÚ±íÖУ¬Êý¾ÝÖ®¼äµÄ²ã´Î¹ØÏµ¼´¸¸×Ó¹ØÏµ£¬Í¨¹ý±íÖеÄÁÐÓëÁмäµÄ¹ØÏµÀ´ÃèÊö£¬
ÈçEMP±íÖеÄEMPNOºÍMGR¡£EMPNO±íʾ¸Ã¹ÍÔ±µÄ±àºÅ£¬MGR±íʾÁìµ¼¸Ã¹ÍÔ±µÄÈ˵ıàºÅ£¬¼´×Ó½ÚµãµÄMGRÖµµÈÓÚ¸¸½ÚµãµÄEMPNOÖµ¡£ÔÚ±íµÄ
ÿһÐÐÖж¼ÓÐÒ»¸ö±íʾ¸¸½ÚµãµÄMGR£¨³ý¸ù½ÚµãÍ⣩£¬Í¨¹ýÿ¸ö½ÚµãµÄ¸¸½Úµã£¬¾Í¿ÉÒÔÈ·¶¨Õû¸öÊ÷½á¹¹¡£
ÔÚSELECTÃüÁîÖÐʹÓÃCONNECT BY ºÍ°ªSTART WITH
×Ó¾ä¿ÉÒÔ²éѯ±íÖеÄÊ÷Ðͽṹ¹ØÏµ¡£ÆäÃüÁî¸ñʽÈçÏ£º
SELECT ¡£¡£¡£
CONNECT BY {PRIOR ÁÐÃû1=ÁÐÃû2|ÁÐÃû1=PRIOR ÁÑÃû2}
[START WITH]£»
ÆäÖУºCONNECT
BY×Ó¾ä˵Ã÷ÿÐÐÊý¾Ý½«Êǰ´²ã´Î˳Ðò¼ìË÷£¬²¢¹æ¶¨½«±íÖеÄÊý¾ÝÁ¬ÈëÊ÷ÐͽṹµÄ¹ØÏµÖС£PRIORYÔËËã·û±ØÐë·ÅÖÃÔÚÁ¬½Ó¹ØÏµµÄÁ½ÁÐÖÐijһ¸öµÄÇ°Ãæ¡£¶ÔÓÚ½Ú
µã¼äµÄ¸¸×Ó¹ØÏµ£¬PRIORÔËËã·ûÔÚÒ»²à±íʾ¸¸½Úµã£¬ÔÚÁíÒ»²à±íʾ×ӽڵ㣬´Ó¶øÈ·¶¨²éÕÒÊ÷½á¹¹ÊǵÄ˳ÐòÊÇ×Ô¶¥ÏòÏ»¹ÊÇ×Ôµ×ÏòÉÏ¡£ÔÚÁ¬½Ó¹ØÏµÖУ¬³ýÁË¿ÉÒÔʹ
ÓÃÁÐÃûÍ⣬»¹ÔÊÐíʹÓÃÁбí´ï
Ïà¹ØÎĵµ£º
update t_tmprpt_firstreplycosttime t
set (t.firstreplytime,
t.dealstaff,
t.firstreplyfailcontent)
= (select a.suggesttime,
a.suggester,
substr(a.remark,instr(a.remark,'¡¿',1)+2)
from t_wf_suggesthis a
......
×î½üÔÚʹÓùý³ÌÖеķ¢ÏÖÁ¬½ÓÊý¾Ý¿âʱºÃʱ»µ£¬¾³£±¨TNSÒì³££¬ÔÚ¿ØÖÆÃæ°åÖÐÔÚ·þÎñÖн«tnsÕý³£Æô¶¯ºó£¬µ±Á¬½ÓÊý¾Ý¿âʱ·¢ÏÖtnsÓÖÍ£Ö¹ÁË¡£¿à¿àÔÚÍøÉÏËÑѰÁËÐí¶à£¬·¢ÏÖÍøÉÏÆÌÌì¸ÇµØµÄÈýÖÖ·½·¨£¬ÎÒÒ»Ò»ÊÔÁ˶¼Ã»ÓÐ×÷Óá£
ºóÀ´ÎÞÒâÖп´µ½Ò»Î»ÍøÓÑ˵Ëû³öÏÖ¹ýÏàͬµÄÎÊÌ⣬ÊÇÓÉÓÚ×°Á˸övpnÈí¼þ£¬ÕâʱµÄÎÒÉñ¾ÌرðÃô ......
select myFunc(²ÎÊý1,²ÎÊý2..) to dual; --¿ÉÒÔÖ´ÐÐһЩҵÎñÂß¼
Ò»:OracleÖеĺ¯ÊýÓë´æ´¢¹ý³ÌµÄÇø±ð:
A:º¯Êý±ØÐëÓзµ»ØÖµ,¶ø¹ý³ÌûÓÐ.
B:º¯Êý¿ÉÒÔµ¥¶ÀÖ´ÐÐ.¶ø¹ý³Ì±ØÐëͨ¹ýexecuteÖ´ÐÐ.
C:º¯Êý¿ÉÒÔǶÈëµ½SQLÓï¾äÖÐÖ´ÐÐ.¶ø¹ý³Ì²»ÐÐ.
ÆäʵÎÒÃÇ¿ÉÒÔ½«±È½Ï¸´ÔӵIJéѯд³Éº¯Êý.È»ºóµ½´æ´¢¹ý³ÌÖÐÈ¥µ÷ÓÃÕâЩº¯Êý.
¶þ:È ......
minusÔËËã
·µ»ØÔÚµÚÒ»¸ö²éѯ½á¹ûÖÐÓëµÚ¶þ¸ö²éѯ½á¹û²»ÏàͬµÄÄDz¿·ÖÐмǼ¡£
ÓÐÄÄЩ¹¤ÖÖÔڲƻᲿÖÐÓУ¬¶øÔÚÏúÊÛ²¿ÖÐûÓУ¿
exp:selectjobfromaccount
minus
selectjobfromsales;
ÓëunionÏà·´ ......