Oracle²ã´Î²éѯºÍ·ÖÎöº¯ÊýÔںŶÎÑ¡È¡ÖеÄÓ¦ÓÃ
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£¨ÃüÁîÐУ©ÖÐÖ´Ð ......
/*============´´½¨Customer±í==========*/
create table Customer
(
Customer_id number(6) not null,
Customer_name varchar2(50) not null,
Password varcha ......
1.
¸ÅÄͬ£º
Á¬½ÓÊÇÖ¸ÎïÀíµÄ¿Í
»§¶Ëµ½oracle·þÎñ¶ËµÄÁ¬½Ó¡£Ò»°ãÊÇͨ¹ýÒ»¸öÍøÂçµÄÁ¬½Ó¡£
ÔÚÒѽ¨Á¢µÄÁ¬½Ó
ÉÏ£¬½¨Á¢¿Í»§¶ËÓëoracle
µÄ»á»°£¬ÒÔºó¿Í
»§¶ËÓëoracle
µÄ½»»¥¶¼ÔÚÒ»¸ö»á»°»·¾³ÖÐ
½øÐС£
2.
¹ØϵÊǶà¶Ô¶à£º[ͬÒâÍøÓѵÄÒâ¼û£¬Ó¦¸ÃÊÇ1¶Ô
¶à¡£Ò»¸ö»á»°ÒªÃ´ ......
ORACLEÈÕÆÚʱ¼äº¯Êý´óÈ«
TO_DATE¸ñʽ(ÒÔʱ¼ä:2007-11-02 13:45:25ΪÀý)
Year:
yy two digits Á½Î»Äê &nb ......