Oracle²ã´Î²éѯºÍ·ÖÎöº¯ÊýÔںŶÎѡȡÖеÄÓ¦ÓÃ
ÕªÒª
Ò»×éÁ¬ÐøµÄÊý£¬È¥µôÖмäһЩÊý£¬ÈçºÎÇó³öʣϵÄÊýµÄÇø¼ä£¨¼´ºÅ¶Î£©£¿ÖªµÀºÅ¶ÎµÄÆðÖ¹£¬ÈçºÎÇó³ö¸ÃºÅ¶ÎÄÚËùÓеÄÊý£¿ÖªµÀÒ»¸ö´óµÄºÅ¶Î·¶Î§ºÍÒѾȡ¹ýµÄºÅ¶Î£¬ÈçºÎÇó³ö¿ÉÓõĺŶΣ¿ÀûÓÃOracleÌṩµÄÇ¿´óµÄ²éѯ¹¦ÄÜÒÔ¼°·ÖÎöº¯Êý£¬ÎÒÃÇ¿ÉÒÔºÜÇáËɵĽâ¾öÉÏÊöÎÊÌâ¡£
¹Ø¼ü´Ê£º
ºÅ¶Îѡȡ¡¢Á¬ÐøÊý¡¢¶Ïµã¡¢²ã´Î²éѯ¡¢·ÖÎöº¯Êý¡¢connect by¡¢rownum¡¢level¡¢lead¡¢lag
1. ÎÊÌâµÄÌá³ö
ÔÚʵ¼Ê¹¤×÷ÖУ¬ÎÒÃdz£³£»áÅöµ½ºÅ¶ÎѡȡµÄÎÊÌ⣬ÀýÈ磺
Ò»×éÁ¬ÐøµÄÊý£¬È¥µôÖмäһЩÊý£¬ÒªÇó³öʣϵÄÊýµÄÇø¼ä£¨¼´ºÅ¶Î£©
ÀýÈ磺һ´®Êý×ÖΪ1,2,3,4,7,9,10£¬ÔòºÅ¶ÎΪ1-4,7-7,9-10
ÖªµÀºÅ¶ÎµÄÆðÖ¹£¬ÒªÇó³ö¸ÃºÅ¶ÎÄÚËùÓеÄÊý
ÀýÈ磺ºÅ¶ÎΪ1-3,15-15£¬ÔòºÅ¶ÎÄÚËùÓеÄÊýΪ1,2,3,15
Ò»×éÊý£¬Öмä¿ÉÄÜÓжϵ㣬ҪÇó³öȱʧµÄÊý
ÀýÈ磺һ´®Êý×ÖΪ1,2,3,4,7,9,10£¬ÔòȱʧµÄÊýΪ5,6,8
ÒÑÖª´óºÅ¶Î·¶Î§¼°ÒÑÓúŶη¶Î§£¬Çó¿ÉÓúŶη¶Î§
ÀýÈ磺´óºÅ¶Î·¶Î§0-999,ÒÑÓúŶη¶Î§0-200,399-599£¬Ôò¿ÉÓúŶη¶Î§Îª201-398,600-999
2. »ù´¡ÖªÊ¶
ÏÈ×öÏÂÈÈÉíÔ˶¯£¬»Ø¹Ëһϲã´Î²éѯºÍlead£¯lagº¯ÊýµÄÔËÓá£
2.1 αÁÐrownumºÍlevel
αÁоÍÊDz¢·ÇÔÚ±íÖÐÕæÕý´æÔÚµÄÁС£ÒÑÓкܶà×ÊÁϽéÉÜrownumºÍlevelÕâÁ½¸öαÁС£ÕâÀïÖ»ÏëÇ¿µ÷Ò»µã£¬Î±ÁÐÊÇÖ»Õë¶Ô½á¹û¼¯µÄ¡£
2.2 ÀûÓòã´Î²éѯ¹¹ÔìÁ¬ÐøµÄÊý
²úÉú5¡«8Õâ4¸öÁ¬ÐøµÄÊý
PHP code:
select * from (select rownum+4 from dual connect by rownum<5);
select * from (select level+4 from dual connect by level<5);
........
ÒÔ8ÔÂΪ½ç£¬ÀýÈç2005Äê8ÔÂ1ÈÕ£¬Ö®Ç°µÄÔÚУѧÉúÈëѧÄê·ÝΪ2001¡«2004£¬Ö®ºóµÄΪ2002¡«2005¡£Çóµ±Ç°ÈÕÆÚϵÄÔÚУѧÉúÈëѧÄê·Ý£º
PHP code:
select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<
OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓá£
Ö´Ðл·¾³£º¿ÉÒÔÔÚSQLPLUS.EXE»òÕßDOS£¨ÃüÁîÐУ©ÖÐÖ´Ð ......