[杞琞瑙ｆ瀽oracle鐨凴OWNUM — 浣滆: chen_liang

Posted by 楗洅 on 2009骞11鏈24鏃 , No comment

瀵逛簬rownum鏉ヨ瀹冩槸oracle绯荤粺椤哄簭鍒嗛厤涓轰粠鏌ヨ杩斿洖鐨勮鐨勭紪鍙凤紝杩斿洖鐨勭涓琛屽垎閰嶇殑鏄1锛岀浜岃鏄2锛屼緷姝ょ被鎺紝杩欎釜浼瓧娈靛彲浠ョ敤浜庨檺鍒舵煡璇㈣繑鍥炵殑鎬昏鏁帮紝鑰屼笖rownum涓嶈兘浠ヤ换浣曡〃鐨勫悕绉颁綔涓哄墠缂銆
涓句緥璇存槑锛
渚嬪琛細student(瀛︾敓)琛紝琛ㄧ粨鏋勪负锛
ID銆銆銆 char(6)銆銆銆銆銆 –瀛﹀彿
name銆銆銆銆VARCHAR2(10)銆銆銆–濮撳悕

  1. CREATE TABLE student (ID char(6), name VARCHAR2(100));
  2. INSERT INTO sale VALUES('200001',鈥樺紶涓鈥);
  3. INSERT INTO sale VALUES('200002',鈥樼帇浜屸);
  4. INSERT INTO sale VALUES('200003',鈥樻潕涓夆);
  5. INSERT INTO sale VALUES('200004',鈥樿档鍥涒);
  6. commit;

(1) rownum 瀵逛簬绛変簬鏌愬肩殑鏌ヨ鏉′欢
濡傛灉甯屾湜鎵惧埌瀛︾敓琛ㄤ腑绗竴鏉″鐢熺殑淇℃伅锛屽彲浠ヤ娇鐢╮ownum=1浣滀负鏉′欢銆備絾鏄兂鎵惧埌瀛︾敓琛ㄤ腑绗簩鏉″鐢熺殑淇℃伅锛屼娇鐢╮ownum=2缁撴灉鏌ヤ笉鍒版暟鎹傚洜涓簉ownum閮芥槸浠1寮濮嬶紝浣嗘槸1浠ヤ笂鐨勮嚜鐒舵暟鍦╮ownum鍋氱瓑浜庡垽鏂槸鏃惰涓洪兘鏄痜alse鏉′欢锛屾墍浠ユ棤娉曟煡鍒皉ownum = n锛坣 > 1鐨勮嚜鐒舵暟锛夈

  1. SQL> SELECT rownum,id,name FROM student WHERE rownum=1;
  2. (鍙互鐢ㄥ湪闄愬埗杩斿洖璁板綍鏉℃暟鐨勫湴鏂癸紝淇濊瘉涓嶅嚭閿欙紝濡傦細闅愬紡娓告爣)

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————
1 200001 寮犱竴

  1. SQL> SELECT rownum,id,name FROM student WHERE rownum =2;

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————

锛2锛塺ownum瀵逛簬澶т簬鏌愬肩殑鏌ヨ鏉′欢
濡傛灉鎯虫壘鍒颁粠绗簩琛岃褰曚互鍚庣殑璁板綍锛屽綋浣跨敤rownum > 2鏄煡涓嶅嚭璁板綍鐨勶紝鍘熷洜鏄敱浜巖ownum鏄竴涓绘槸浠1寮濮嬬殑浼垪锛孫racle 璁や负rownum > n(n > 1鐨勮嚜鐒舵暟)杩欑鏉′欢渚濇棫涓嶆垚绔嬶紝鎵浠ユ煡涓嶅埌璁板綍

  1. SQL> SELECT rownum,id,name FROM student WHERE rownum >2;

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————

閭ｅ浣曟墠鑳芥壘鍒扮浜岃浠ュ悗鐨勮褰曞憖銆傚彲浠ヤ娇鐢ㄤ互涓嬬殑瀛愭煡璇㈡柟娉曟潵瑙ｅ喅銆傛敞鎰忓瓙鏌ヨ涓殑rownum蹇呴』瑕佹湁鍒悕锛屽惁鍒欒繕鏄笉浼氭煡鍑鸿褰曟潵锛岃繖鏄洜涓簉ownum涓嶆槸鏌愪釜琛ㄧ殑鍒楋紝濡傛灉涓嶈捣鍒悕鐨勮瘽锛屾棤娉曠煡閬搑ownum鏄瓙鏌ヨ鐨勫垪杩樻槸涓绘煡璇㈢殑鍒椼

  1. SQL>select * FROM(SELECT rownum no ,id,name FROM student) WHERE no>2;

杩斿洖:
NO ID NAME
———- —— —————————————————
3 200003 鏉庝笁
4 200004 璧靛洓

  1. SQL> SELECT * FROM(SELECT rownum,id,name FROM student)WHERE rownum > 2;

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————

锛3锛塺ownum瀵逛簬灏忎簬鏌愬肩殑鏌ヨ鏉′欢
濡傛灉鎯虫壘鍒扮涓夋潯璁板綍浠ュ墠鐨勮褰曪紝褰撲娇鐢╮ownum < 3鏄兘寰楀埌涓ゆ潯璁板綍鐨勩傛樉鐒秗ownum瀵逛簬rownum 1鐨勮嚜鐒舵暟锛夌殑鏉′欢璁や负鏄垚绔嬬殑锛屾墍浠ュ彲浠ユ壘鍒拌褰曘

  1. SQL> SELECT rownum,id,name FROM student WHERE rownum < 3;

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————
1 200001 寮犱竴
2 200002 鐜嬩簩

缁间笂鍑犵鎯呭喌锛屽彲鑳芥湁鏃跺欓渶瑕佹煡璇ownum鍦ㄦ煇鍖洪棿鐨勬暟鎹紝閭ｆ庝箞鍔炲憖浠庝笂鍙互鐪嬪嚭rownum瀵瑰皬浜庢煇鍊肩殑鏌ヨ鏉′欢鏄汉涓簍rue鐨勶紝rownum瀵逛簬澶т簬鏌愬肩殑鏌ヨ鏉′欢鐩存帴璁や负鏄痜alse鐨勶紝浣嗘槸鍙互闂存帴鐨勮瀹冭浆涓鸿涓烘槸true鐨勩傞偅灏卞繀椤讳娇鐢ㄥ瓙鏌ヨ銆備緥濡傝鏌ヨrownum鍦ㄧ浜岃鍒扮涓夎涔嬮棿鐨勬暟鎹紝鍖呮嫭绗簩琛屽拰绗笁琛屾暟鎹紝閭ｄ箞鎴戜滑鍙兘鍐欎互涓嬭鍙ワ紝鍏堣瀹冭繑鍥炲皬浜庣瓑浜庝笁鐨勮褰曡锛岀劧鍚庡湪涓绘煡璇腑鍒ゆ柇鏂扮殑rownum鐨勫埆鍚嶅垪澶т簬绛変簬浜岀殑璁板綍琛屻備絾鏄繖鏍风殑鎿嶄綔浼氬湪澶ф暟鎹泦涓奖鍝嶉熷害.

  1. SQL> SELECT * FROM
  2.  (SELECT rownum no,id,name FROM student WHERE rownum < =3 )  WHERE no > =2;

杩斿洖:
NO ID NAME
———- —— —————————————————
2 200002 鐜嬩簩
3 200003 鏉庝笁

锛4锛塺ownum鍜屾帓搴
Oracle涓殑rownum鐨勬槸鍦ㄥ彇鏁版嵁鐨勬椂鍊欎骇鐢熺殑搴忓彿锛屾墍浠ユ兂瀵规寚瀹氭帓搴忕殑鏁版嵁鍘绘寚瀹氱殑rowmun琛屾暟鎹氨蹇呴』娉ㄦ剰浜嗐

  1. SQL> SELECT rownum ,id,name FROM student ORDER BY name;

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————
3 200003 鏉庝笁
2 200002 鐜嬩簩
1 200001 寮犱竴
4 200004 璧靛洓

鍙互鐪嬪嚭锛宺ownum骞朵笉鏄寜鐓ame鍒楁潵鐢熸垚鐨勫簭鍙枫傜郴缁熸槸鎸夌収璁板綍鎻掑叆鏃剁殑椤哄簭缁欒褰曟帓鐨勫彿锛宺owid涔熸槸椤哄簭鍒嗛厤鐨勩備负浜嗚В鍐宠繖涓棶棰橈紝蹇呴』浣跨敤瀛愭煡璇

  1. SQL> SELECT rownum ,id,name
  2. FROM (SELECT * FROM student ORDER BY name);

杩斿洖:
ROWNUM ID NAME
———- —— —————————————————
1 200003 鏉庝笁
2 200002 鐜嬩簩
3 200001 寮犱竴
4 200004 璧靛洓

杩欐牱灏辨垚浜嗘寜name鎺掑簭锛屽苟涓旂敤rownum鏍囧嚭姝ｇ‘搴忓彿锛堟湁灏忓埌澶э級
绗旇呭湪宸ヤ綔涓湁涓涓婄櫨涓囨潯璁板綍鐨勮〃锛屽湪jsp椤甸潰涓渶瀵硅琛ㄨ繘琛屽垎椤垫樉绀猴紝 渚胯冭檻鐢╮ownum鏉ヤ綔锛屼笅闈㈡槸鍏蜂綋鏂规硶(姣忛〉
鏄剧ず20鏉):

  1. SQL> SELECT * FROM tabname WHERE rownum < 20 ORDER BY name

浣嗗嵈鍙戠幇oracle鍗翠笉鑳芥寜鑷繁鐨勬剰鎰挎潵鎵ц锛岃屾槸鍏堥殢渚
鍙20鏉¤褰曪紝鐒跺悗鍐 order by锛屽悗缁忓挩璇racle,璇磖ownum纭疄灏辫繖鏍凤紝鎯崇敤鐨勮瘽锛屽彧鑳界敤瀛愭煡璇 鏉ュ疄鐜板厛鎺掑簭锛屽悗
rownum锛屾柟娉曞涓:

  1. SQL> SELECT * FROM (SELECT * FROM tabname ORDER BY name)
  2. WHERE  rownum < 20

浣嗚繖鏍蜂竴鏉ワ紝鏁堢巼浼氳緝浣庡緢澶氥

鍚庣粡绗旇呰瘯楠岋紝鍙渶鍦╫rder by 鐨勫瓧娈典笂鍔犱富閿垨绱㈠紩鍗冲彲璁﹐racle鍏堟寜 璇ュ瓧娈垫帓搴忥紝鐒跺悗鍐峳ownum,鏂规硶涓嶅彉

鍙栧緱鏌愬垪涓N澶х殑琛:

  1. SQL> SELECT column_name FROM
  2. (SELECT table_name.*,dense_rank() over (ORDER BY COLUMN DESC) rank FROM table_name)
  3. WHERE rank = N;

鍋囧瑕佽繑鍥炲墠5鏉¤褰:

  1. SQL> SELECT * FROM tablename WHERE rownum < 6;
  2. (鎴栨槸rownum < = 5 鎴栨槸rownum != 6)

鍋囧瑕佽繑鍥炵5-9鏉¤褰:

  1. SQL> SELECT * FROM tablename
  2. WHERE 鈥
  3. AND rownum < 10  
  4. minus  
  5. SELECT * FROM tablename  WHERE 鈥  AND rownum > 5
  6. ORDER BY name

閫夊嚭缁撴灉鍚庣敤name鎺掑簭鏄剧ず缁撴灉銆(鍏堥夊啀鎺掑簭)

娉ㄦ剰锛氬彧鑳界敤浠ヤ笂绗﹀彿( "< " ,"<=","!=")銆

  1. SQL> SELECT * FROM tablename WHERE rownum != 10
  2.  
  3. 杩斿洖鐨勬槸鍓嶏紮鏉¤褰曘
  4. 涓嶈兘鐢細">" ,">=" ,"=","Between...and"銆傜敱浜巖ownum鏄竴涓绘槸浠1寮濮嬬殑浼垪锛孫racle 璁や负杩欑鏉′欢 涓嶆垚绔嬶紝鏌ヤ笉鍒拌褰.
  5. 鍙﹀锛岃繖涓柟娉曟洿蹇:
  6. <pre lang="sql">
  7. SQL> SELECT * FROM (
  8. SELECT rownum r,a FROM yourtable
  9. WHERE rownum < = 20  ORDER BY name )  WHERE r > 10

杩欐牱鍙栧嚭绗11-20鏉¤褰!(鍏堥夊啀鎺掑簭鍐嶉)

瑕佸厛鎺掑簭鍐嶉夊垯椤荤敤select宓屽锛氬唴灞傛帓搴忓灞傞夈
rownum鏄殢鐫缁撴灉闆嗙敓鎴愮殑锛屼竴鏃︾敓鎴愶紝灏变笉浼氬彉鍖栦簡锛涘悓鏃,鐢熸垚鐨勭粨鏋滄槸渚濇閫掑姞鐨勶紝娌℃湁1灏辨案杩滀笉浼氭湁2!
rownum 鏄湪 鏌ヨ闆嗗悎浜х敓鐨勮繃绋嬩腑浜х敓鐨勪吉鍒楋紝骞朵笖濡傛灉where鏉′欢涓瓨鍦 rownum 鏉′欢鐨勮瘽锛屽垯:
1锛 鍋囧 鍒ゅ畾鏉′欢鏄父閲忥紝鍒欙細
鍙兘 rownum = 1, < = 澶т簬1 鐨勮嚜鐒舵暟锛 = 澶т簬1 鐨勬暟鏄病鏈夌粨鏋滅殑锛 澶т簬涓涓暟涔熸槸娌℃湁缁撴灉鐨
鍗 褰撳嚭鐜颁竴涓 rownum 涓嶆弧瓒虫潯浠剁殑鏃跺欏垯 鏌ヨ缁撴潫

2: 褰撳垽瀹氬间笉鏄父閲忕殑鏃跺
鑻ユ潯浠舵槸 = var , 鍒欏彧鏈夊綋 var 涓1 鐨勬椂鍊欐墠婊¤冻鏉′欢锛岃繖涓椂鍊欎笉瀛樺湪 stop key ,蹇呴』杩涜 full scan ,瀵规瘡涓弧瓒冲叾浠杦here鏉′欢鐨勬暟鎹繘琛屽垽瀹
閫夊嚭涓琛屽悗鎵嶈兘鍘婚塺ownum=2鐨勮鈥︹

Tags : , , , , , ,

Filed under : 鏁版嵁搴 | Trackback URI | Comments RSS