Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle ÖеÄÊ÷²éѯºÍ connect by


Oracle ÖеÄÊ÷²éѯºÍ connect by
ʹÓà connect by ºÍ start with À´½¨Á¢ÀàËÆÓÚÊ÷µÄ±¨±í²¢²»ÄÑ£¬Ö»Òª×ñÑ­ÒÔÏ»ù±¾Ô­Ôò¼´¿É£º
ʹÓà connect by ʱ¸÷×Ó¾äµÄ˳ÐòӦΪ£º
select
from
where
start with
connect by
order by
prior ʹ±¨±íµÄ˳ÐòΪ´Ó¸ùµ½Ò¶£¨Èç¹û prior ÁÐÊǸ¸±²£©»ò´ÓÒ¶µ½¸ù£¨Èç¹û prior ÁÐÊǺó´ú£©¡£
where ×Ó¾ä¿ÉÒÔ´ÓÊ÷ÖÐÅųý¸öÌ壬µ«²»ÅųýËüÃǵÄ×ÓË»òÕß׿ÏÈ£¬Èç¹û prior ÁÐÊǺó´ú£©¡£
connect by ÖеÄÌõ¼þ£¨ÓÈÆäÊDz»µÈÓÚ£©Ïû³ý¸öÌåºÍËüËùÓеÄ×ÓË»ò׿ÏÈ£¬ÒÀÀµÓÚÔõÑù¸ú×ÙÊ÷£©¡£
connect by ²»ÄÜÓë where ×Ó¾äÖеıíÁ¬½ÓÔÚÒ»ÆðʹÓá£
 
ÏÂÃæÊǼ¸¸öÀý×Ó
1. ´Ó¸ùµ½Ò¶±éÀú
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;
2. ´ÓÒ¶µ½¸ù±éÀú
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 300
CONNECT BY n_id = PRIOR n_parendid;
3. Åųý¸öÌ壬µ«²»ÅųýËüÃǵÄ×ÓËï
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL AND n_id != 2
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id;
4. Ïû³ý¸öÌåºÍËüËùÓеÄ×ÓËï
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id AND n_id != 2;
5. ¸Ä±äÏÔʾ˳Ðò
SELECT n_parendid, n_name, (LEVEL - 1), n_id
from navigation
WHERE n_parendid IS NOT NULL
START WITH n_id = 0
CONNECT BY n_parendid = PRIOR n_id
ORDER BY n_viewnum DESC; 
±¾ÎÄת×Ôcsdn:http://blog.csdn.net/wzy0623/archive/2007/06/18/1656345.aspx


Ïà¹ØÎĵµ£º

oracle ±í¿Õ¼ä²Ù×÷

oracle±í¿Õ¼ä²Ù×÷Ïê½â
  1
  2
  3×÷Õߣº   À´Ô´£º    ¸üÐÂÈÕÆÚ£º2006-01-04 
  5
  6 
  7½¨Á¢±í¿Õ¼ä
  8
  9CREATE TABLESPACE data01
 10DATAFILE '/ora ......

ORACLEÊý¾Ýµ¼Èë

ÔÚÀûÓÃNETWORK_LINK·½Ê½µ¼³öµÄʱºò£¬³öÏÖÁËÕâ¸ö´íÎó¡£
Ïêϸ´íÎóÐÅÏ¢ÈçÏ£º
bash-3.00$ expdp yangtk/yangtk directory=d_temp dumpfile=jiangsu.dp network_link=test113 logfile=jiangsu.log tables=cat_org
Export: Release11.1.0.6.0 - 64bit Production onÐÇÆÚ¶þ, 16 9ÔÂ, 2008 17:08:22
Copyright (c) 2003, 2007, ......

°²×°Oracle¼ì²âlinuxÄں˲ÎÊý

Checking kernel parameters
Checking for semmsl=250; found semmsl=250.    Passed
Checking for semmns=32000; found semmns=32000.    Passed
Checking for semopm=100; found semopm=32.    Failed <<<<
Checking for semmni=128; found semmni=128.&nb ......

ORACLE SQLÐÔÄÜÓÅ»¯´ó×ܽá

ORACLE SQLÐÔÄÜÓÅ»¯´ó×ܽá
1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
" ?; J) n+ v8 J- v" q    OracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡ ......

oracle Öлָ´ÎóɾµÄ±í

ºÜ²»ÐÒ£¬½¨Á¢µÄ±í±»Ä³ÈËÎóɾÁË£¬£¬£¬
»¹ºÃ¿ÉÒÔͨ¹ýÕâÑù»Ö¸´¡£
select * from user_recyclebin where original_name like 'FINANCE_%'   order by droptime desc;
FLASHBACK TABLE FINANCE_CASE_FEE_ITEM TO BEFORE DROP
¼´ËùÓÐdropµÄ±í¶¼ÔÚ user_recyclebin  Õâ¸öoracle»ØÊÕÕ¾ÀïÃæµÄ£¬ÔÙͨ¹ýflashbackÃü ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ