Oracle µÄrownum ÔÀíºÍʹÓà ¼°ÓërowidµÄÇø±ð
Oracle µÄrownum ÔÀíºÍʹÓÃ
ÔÚ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, then Oracle 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;£¨¿ÉÒÔÓÃÔÚÏÞÖÆ·µ»Ø¼Ç¼ÌõÊýµÄµØ·½£¬±£Ö¤²»³ö´í£¬È磺ÒþʽÓα꣩
SQL> select rownum ,id,name from student where rownum =1;
ROWNUM ID NAME
---
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
Oracle
SqlServer2000 2005
MySql
DB2
ORACLE sun SUN
Êý¾Ý¿â DataBase db
´æ´¢Êý¾Ý
Êý¾Ý Data
Êý×Ö   ......
Mysql£¬SqlServer£¬OracleÖ÷¼ü×Ô¶¯Ôö³¤µÄÉèÖÃ
1¡¢°ÑÖ÷¼ü¶¨ÒåΪ×Ô¶¯Ôö³¤±êʶ·ûÀàÐÍ
ÔÚmysqlÖУ¬Èç¹û°Ñ±íµÄÖ÷¼üÉèΪauto_incrementÀàÐÍ£¬Êý¾Ý¿â¾Í»á×Ô¶¯ÎªÖ÷¼ü¸³Öµ¡£ÀýÈ磺
create table customers(id int auto_increment primary key not null, name varchar(15));
insert into customers(name) values("name1"),("nam ......
oracel sqlplus
1). ÉèÖõ±Ç°sessionÊÇ·ñ¶ÔÐ޸ĵÄÊý¾Ý½øÐÐ×Ô¶¯Ìá½»
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)£®ÔÚÓÃstartÃüÁîÖ´ÐÐÒ»¸ösql½Å±¾Ê±£¬ÊÇ·ñÏÔʾ½Å±¾ÖÐÕýÔÚÖ´ÐеÄSQLÓï¾ä
SQL> SET ECHO {ON|OFF}
3).ÊÇ·ñÏÔʾµ±Ç°sqlÓï¾ä²éѯ»òÐ޸ĵÄÐÐÊý
SQL> SET FEED[BACK] {6|n|ON|OFF}
ĬÈÏÖ»Óн ......
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) ±íʾ¸ù¾ÝCOL1·Ö×飬ÔÚ·Ö×éÄÚ²¿¸ù¾Ý COL2ÅÅÐò£¬¶ø´Ëº¯Êý¼ÆËãµÄÖµ¾Í±íʾÿ×éÄÚ²¿ÅÅÐòºóµÄ˳Ðò±àºÅ£¨×éÄÚÁ¬ÐøµÄΨһµÄ).
ÓërownumµÄÇø±ðÔÚÓÚ£ºÊ¹ÓÃrownum½øÐÐÅÅÐòµÄʱºòÊÇÏȶԽá¹û¼¯¼ÓÈëαÁÐrownumÈ»ºóÔÙ½øÐÐÅÅÐò£¬¶ø´Ëº¯ÊýÔÚ°üº¬ÅÅÐò´Ó¾äºóÊÇÏÈÅÅÐòÔÙ¼ÆËãÐкÅ ......