OracleµÄrownumÔÀíºÍʹÓÃ
ÔÎijö×Ô£ºhttp://tenn.javaeye.com/blog/99339
ÔÚOracle
ÖУ¬Òª°´Ìض¨Ìõ¼þ²éѯǰNÌõ¼Ç¼£¬Óøörownum
¾Í¸ã¶¨ÁË¡£
select * from emp where rownum
<= 5
¶øÇÒÊéÉÏÒ²¸æ½ë£¬²»ÄܶÔrownum
ÓÃ">"£¬ÕâÒ²¾ÍÒâζ×Å£¬Èç¹ûÄãÏëÓÃ
select * from emp where rownum
> 5
ÔòÊÇʧ°ÜµÄ¡£ÒªÖªµÀΪʲô»áʧ°Ü£¬ÔòÐèÒªÁ˽ârownum
±³ºóµÄ»úÖÆ£º
1 Oracle
executes your query.
2 Oracle
fetches the first row and calls it row number 1.
3 Have we gotten past row number meets the criteria? If no, then Oracle
discards the row, If yes, thenOracle
return the row.
4 Oracle
fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5 Go to step 3.
Á˽âÁËÔÀí£¬¾ÍÖªµÀrownum
>²»»á³É¹¦£¬ÒòΪÔÚµÚÈý²½µÄʱºò²éѯ³öµÄÐÐÒѾ±»¶ªÆú£¬µÚËIJ½²é³öÀ´µÄrownum
ÈÔÈ»ÊÇ1,ÕâÑùÓÀÔ¶Ò²²»»á³É¹¦¡£
ͬÑùµÀÀí£¬rownum
Èç¹ûµ¥¶ÀÓÃ=,Ò²Ö»ÓÐÔÚrownum
=1ʱ²ÅÓÐÓá£
¶ÔÓÚrownum
À´ËµËüÊÇoracle
ϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖÆ²éѯ·µ»ØµÄ×ÜÐÐÊý£¬¶øÇÒrownum
²»ÄÜÒÔÈκαíµÄÃû³Æ×÷Ϊǰ׺¡£
¾ÙÀý˵Ã÷£º
ÀýÈç±í£ºstudent(ѧÉú)±í£¬±í½á¹¹Îª£º
ID¡¡¡¡¡¡ char(6)¡¡¡¡¡¡¡¡¡¡ --ѧºÅ
name¡¡¡¡¡¡¡¡VARCHAR2(10)¡¡¡¡¡¡--ÐÕÃû create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘ÕÅÒ»’);
insert into sale values('200002',‘Íõ¶þ’);
insert into sale values('200003',‘ÀîÈý’);
insert into sale values('200004',‘ÕÔËÄ’);
commit;
(1) rownum
¶ÔÓÚµÈÓÚijֵµÄ²éѯÌõ¼þ
Èç¹ûÏ£ÍûÕÒµ½Ñ§Éú±íÖеÚÒ»ÌõѧÉúµÄÐÅÏ¢£¬¿ÉÒÔʹÓÃrownum
=1×÷ΪÌõ¼þ¡£µ«ÊÇÏëÕÒµ½Ñ§Éú±íÖеڶþÌõѧÉúµÄÐÅÏ¢£¬Ê¹ÓÃrownum
=2½á¹û²é²»µ½Êý¾Ý¡£ÒòΪrownum
¶¼ÊÇ´Ó1¿ªÊ¼£¬µ«ÊÇ1ÒÔÉϵÄ×ÔÈ»ÊýÔÚrownum
×öµÈÓÚÅжÏÊÇʱÈÏΪ¶¼ÊÇfalseÌõ¼þ£¬ËùÒÔÎÞ·¨²éµ½rownum
= n£¨n>1µÄ×ÔÈ»Êý£©¡£
SQL> select rownum
,id,name from student where rownum
=1;£¨¿ÉÒÔÓÃÔÚÏ
Ïà¹ØÎĵµ£º
-- Create the user
create user SMCQUERY
identified by SMCQUERY;
-- Grant/Revoke role privileges
grant connect to SMCQUERY;
-- Grant/Revoke system privileges
grant select any table to SMCQUERY;
grant debug any procedure to SMCQUERY;
grant debug connect session to SMCQUERY;
grant cr ......
Ê×ÏȸãÇåϼ¸¸ö¸ÅÄ
ORACLEÖУ¬Ô¼Êø·Ödeferred ¸ú immediate 2ÖÖ£º
deferred:Èç¹û Oracle ÔÚÊÂÎñÌá½»£¨commit£©Ê±²Å¶ÔÔ¼ÊøÖ´Ðмì²é£¬Ôò³Æ´ËÔ¼ÊøÊÇÑӳٵģ¨deferred£©¡£Èç¹ûÊý¾ÝÎ¥·´ÁËÑÓ³ÙÔ¼Êø£¬Ìá½»²Ù×÷½«µ¼ÖÂÊÂÎñ±»»Ø¹ö£¨undo£©¡£
immediate:Èç¹ûÔ¼ÊøÊǼ´Ê±µÄ£¨immediate£©£¨·ÇÑӳٵģ©£¬Ôò´ËÔ¼Êø½«ÔÚ ......
ÎÒÓõÄÊÇCentos5.4 DVD¹âÅ̰²×°µÄlinux²Ù×÷ϵͳ£¬°²×°linuxµÄʱºòÑ¡ÉÏ¿ª·¢¹¤¾ß£¬Xmanager,ÓëÊý¾Ý¿âÏà¹ØµÄ°ü¡£
²Ù×÷ϵͳ°²×°Íê³ÉÖ®ºóÐèÒª½øÐÐһϵÁеÄÅäÖòÅÄܰ²×°oracle10g£¬ÏÂÃæ°ÑÖ÷Òª²½Öè¼Ç¼ÏÂÀ´¡£
1.°²×°Íê²Ù×÷ϵͳ֮ºó»¹ÊÇÓÐЩ°üûÓа²×°£¬È»¶ø°²×°oracle10gµÄʱºòÐèÒªÓõ½£¬Ã»Óа²×°µÄ°üÓÐ:
libXp-1.0.0-8.i386.rp ......
1¡£select * from a where a.rowid=(select min(b.rowid) from b where a.id=b.id);
create test1(
nflowid number primary key,
ndocid number,
drecvdate date);
insert into test1 values (1, 12301, sysdate) ;
insert into test1 values (2, 12301, sysdate);
select * from test1 order by drecvdate:
......
ÔÚInternetÉÏÔË×÷Êý¾Ý¿â¾³£»áÓÐÕâÑùµÄÐèÇ󣺰ѱ鲼ȫ¹ú¸÷³ÇÊÐÏàËÆµÄÊý¾Ý¿âÓ¦ÓÃͳһÆðÀ´£¬Ò»¸ö½ÚµãµÄÊý¾Ý¸Ä±ä²»½öÌåÏÖÔÚ±¾µØ£¬»¹·´Ó³µ½Ô¶¶Ë¡£¸´ÖƼ¼Êõ¸øÓû§ÌṩÁËÒ»ÖÖ¿ìËÙ·ÃÎʹ²ÏíÊý¾ÝµÄ°ì·¨¡£
Ò»¡¢ÊµÏÖÊý¾Ý¿â¸´ÖƵÄǰÌáÌõ¼þ
1¡¢Êý¾Ý¿âÖ§³Ö¸ß¼¶¸´Öƹ¦ÄÜ
Äú¿ÉÒÔÓÃsystemÉí·ÝµÇ¼Êý¾Ý¿â£¬²é¿´v$optionÊÓͼ£¬Èç¹ûÆäÖÐAdv ......