oracle¶ÔtopµÄÖ§³Ö
1.ÔÚORACLEÖÐʵÏÖSELECT TOP N
ÓÉÓÚORACLE²»Ö§³ÖSELECT TOPÓï¾ä£¬ËùÒÔÔÚORACLEÖо³£ÊÇÓÃORDER BY¸úROWNUMµÄ×éºÏÀ´ÊµÏÖSELECT TOP NµÄ²éѯ¡£
¼òµ¥µØËµ£¬ÊµÏÖ·½·¨ÈçÏÂËùʾ£º
SELECT¡¡ÁÐÃû£±£®£®£®ÁÐÃû£î¡¡from
(SELECT¡¡ÁÐÃû£±£®£®£®ÁÐÃû£î¡¡from ±íÃû ORDER BY ÁÐÃû£±£®£®£®ÁÐÃû£î)
WHERE ROWNUM <= N£¨³é³ö¼Ç¼Êý£©
ORDER BY ROWNUM ASC
ÏÂÃæ¾Ù¸öÀý×Ó¼òµ¥ËµÃ÷һϡ£
¹Ë¿Í±ícustomer(id,name)ÓÐÈçÏÂÊý¾Ý£º
ID NAME
01 first
02 Second
03 third
04 forth
05 fifth
06 sixth
07 seventh
08 eighth
09 ninth
10 tenth
11 last
Ôò°´NAMEµÄ×Öĸ˳³é³öǰÈý¸ö¹Ë¿ÍµÄSQLÓï¾äÈçÏÂËùʾ£º
SELECT * from
(SELECT * from CUSTOMER ORDER BY NAME)
WHERE ROWNUM <= 3
ORDER BY ROWNUM ASC
Êä³ö½á¹ûΪ£º
ID NAME
08 eighth
05 fifth
01 first
2.ÔÚTOP N¼Í¼Öгé³öµÚM£¨M <= N£©Ìõ¼Ç¼
Ôڵõ½ÁËTOP NµÄÊý¾ÝÖ®ºó£¬ÎªÁ˳é³öÕâNÌõ¼Ç¼ÖеĵÚMÌõ¼Ç¼£¬ÎÒÃÇ¿ÉÒÔ¿¼ÂÇ´ÓROWNUM×ÅÊÖ¡£ÎÒÃÇÖªµÀ£¬ROWNUMÊǼǼ±íÖÐÊý¾Ý±àºÅµÄÒ»¸öÒþ²Ø×ӶΣ¬ËùÒÔ¿ÉÒÔÔڵõ½TOP NÌõ¼Ç¼µÄʱºòͬʱ³é³ö¼Ç¼µÄROWNUM£¬È»ºóÔÙ´ÓÕâNÌõ¼Ç¼ÖгéÈ¡¼Ç¼±àºÅΪMµÄ¼Ç¼£¬¼´Ê¹ÎÒÃÇÏ£ÍûµÃµ½µÄ½á¹û¡£
´ÓÉÏÃæµÄ·ÖÎö¿ÉÒÔºÜÈÝÒ׵õ½ÏÂÃæµÄSQ
Ïà¹ØÎĵµ£º
linux ÉϵÄoracle sqlplus ²»ÄÜÀûÓà ÉÏ, Ï ¼üÀ´²é¿´ÃüÁËÑË÷µ½½â¾öÎÊÌâµÄ°ì·¨£¬ÕûÀíÈçÏÂ
°²×°Èí¼þrlwrap¿ÉÒÔ½â¾öÕâ¸öÎÊÌ⣬¸ÃÈí¼þÊÇÓÃcдµÄ³ÌÐò
¹Ù·½ÏÂÔØµØÖ·£ºhttp://utopia.knoware.nl/~hlub/uck/rlwrap/
°²×°¹ý³Ì£º
ÎÒÃÇÒ²¿ÉÒԲ鿴½âѹºóµÄtar°ü£¬²é¿´README°ïÖúÎļþ
shell>tar -zxvf rlwrap-0.36.tar.gz
sh ......
ÓкܶàÓ¦ÓÃÏîÄ¿, ¸ÕÆð²½µÄʱºòÓÃMYSQLÊý¾Ý¿â»ù±¾ÉÏÄÜʵÏÖ¸÷ÖÖ¹¦ÄÜÐèÇó£¬Ëæ×ÅÓ¦ÓÃÓû§µÄÔö¶à£¬Êý¾ÝÁ¿µÄÔö¼Ó£¬MYSQL½¥½¥µØ³öÏÖ²»¿°ÖظºµÄÇé¿ö£ºÁ¬½ÓºÜÂýÉõÖÁå´»ú£¬ÓÚÊǾÍÓаÑÊý¾Ý´ÓMYSQLǨµ½ORACLEµÄÐèÇó£¬Ó¦ÓóÌÐòÒ²ÒªÏàÓ¦×öһЩÐ޸ġ£±¾ÈË×ܽá³öÒÔϼ¸µã×¢ÒâÊÂÏϣÍû¶Ô´ó¼ÒÓÐËù°ïÖú¡£
1.×Ô¶¯Ôö³¤µÄÊý¾ÝÀàÐÍ´¦Àí
MYSQLÓÐ ......
ÔÚÎÒµÄÉÏÒ»¸öÒøÐÐÏîÄ¿ÖУ¬ÎÒ½Óµ½±àдORACLE´æ´¢¹ý³ÌµÄÈÎÎñ£¬ÎÒÊdzÌÐòÔ±£¬ÄÔ´üÀïÖ»ÓÐһЩÈçºÎʹÓÃCALLABLE½Ó¿Úµ÷Óô洢¹ý³ÌµÄ¾Ñ飬һʱ²»ÖªÈçºÎÏÂÊÖ£¬ÎÒ²éÔÄÁËһЩ×ÊÁÏ£¬Í¨¹ýʵ¼ù·¢ÏÖ±àдORACLE´æ´¢¹ý³ÌÊǷdz£²»ÈÝÒ׵Ť×÷£¬¼´Ê¹ÉÏ·ÒԺ󣬵÷ÊÔºÍÑéÖ¤·Ç³£Âé·³¡£¼òµ¥µØ½²£¬Oracle´æ´¢¹ý³Ì¾ÍÊÇ´æ´¢ÔÚOracleÊý¾Ý¿âÖеÄÒ»¸ö³ÌÐò ......
ÒÔdbaµÇ½
1.²é¿´Äĸö±í±»Ëø:
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
2.²é¿´±»Ëø±íµÄsid ºÍserial# :
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1 ......
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔÓÃNOT EXISTS»òÕßÍâÁ¬½Ó+Ìæ´ú¡£
OracleÔÚÖ´ÐÐIN×Ó²éѯʱ£¬Ê×ÏÈÖ´ÐÐ×Ó²éѯ£¬½«²éѯ½á¹û·ÅÈëÁÙʱ±íÔÙÖ´ÐÐÖ÷²éѯ¡£¶øEXISTÔòÊÇÊ×Ïȼì²éÖ÷²éѯ£¬È»ºóÔËÐÐ×Ó²éѯֱµ½ÕÒµ½µÚÒ»¸öÆ¥ÅäÏî¡£NOT EXISTS±ÈNOT INЧÂÊÉԸߡ£µ«¾ßÌåÔÚÑ¡ÔñIN»òEXIST² ......