开贴讲一下自己的理解
先建一个表
CREATE TABLE TEST(ID NUMBER PRIMARY KEY NOT NULL, NAME VARCHAR2(15));
/
INSERT INTO TEST VALUES(1, 'John');
INSERT INTO TEST VALUES(4, 'Tim');
INSERT INTO TEST VALUES(2, 'Julie');
INSERT INTO TEST VALUES(3, 'Stacy');
INSERT INTO TEST VALUES(6, 'Rahul');
INSERT INTO TEST VALUES(8, 'Leena');
INSERT INTO TEST VALUES(7, 'Amy');
INSERT INTO TEST VALUES(11, 'Bill');
INSERT INTO TEST VALUES(9, 'Teri');
INSERT INTO TEST VALUES(5, 'Julie');
/
注意ID为2和ID为5的NAME是一样的, 有意为之
1, 当ROWNUM单独使用时, 它是按照数据的物理存储位置来赋予NUM号的,
根据ROWID, 我们可以加以验证, 如下SQL:
SQL> SELECT ID, NAME, ROWID FROM TEST WHERE ROWNUM < 6 ;
ID NAME ROWID
---------- --------------- ------------------
1 John AAADm2AABAAAGJiAAA
4 Tim AAADm2AABAAAGJiAAB
2 Julie AAADm2AABAAAGJiAAC
3 Stacy AAADm2AABAAAGJiAAD
6 Rahul AAADm2AABAAAGJiAAE
注意ROWID的顺序
2, 当ROWNUM与ORDER BY联合使用时, 分两种情况:
a,) 当ROWNUM与ORDER BY处于同一查询语句时, Oracle 是先给记录一个ROWNUM再进行ORDER BY,
如下情况:
SQL> SELECT ID, NAME, ROWID FROM TEST WHERE ROWNUM < 6 ORDER BY NAME;
ID NAME ROWID
---------- --------------- ------------------
1 John AAADm2AABAAAGJiAAA
2 Julie AAADm2AABAAAGJiAAC
6 Rahul AAADm2AABAAAGJiAAE
3 Stacy AAADm2AABAAAGJiAAD
4 Tim AAADm2AABAAAGJiAAB
注意ROWID, 先取前面5笔记录 AAADm2AABAAAGJiAAA 至 AAADm2AABAAAGJiAAE
再ORDER BY
b,) 当ROWNUM与ORDER BY不处于同一查询语句,ORDER BY在子查询而ROWNUM在外层时,
Oracle是先对记录一个ROWNUM再进行ORDER BY再给记录赋予一个ROWNUM.
如下情况:
SQL> SELECT *
2 FROM (SELECT ID, NAME, ROWID FROM TEST ORDER BY NAME)
3 WHERE ROWNUM < 6;
ID NAME ROWID
---------- --------------- ------------------
7 Amy AAADm2AABAAAGJiAAG
11 Bill AAADm2AABAAAGJiAAH
1 John AAADm2AABAAAGJiAAA
2 Julie AAADm2AABAAAGJiAAC
5 Julie AAADm2AABAAAGJiAAJ
这个,...呃, 咋说呢, 一目了然
3, 当ROWNUM与ORDER BY处于同一查询语句时, 根据ORDER BY的字段是否为PRKMARY KEY, 又分两种情况.
先删除TEST中的记录, 再INSERT如下记录
INSERT INTO TEST VALUES(99, 'John');
INSERT INTO TEST VALUES(1, 'John');
INSERT INTO TEST VALUES(4, 'Tim');
INSERT INTO TEST VALUES(2, 'Julie');
INSERT INTO TEST VALUES(3, 'Stacy');
INSERT INTO TEST VALUES(6, 'Rahul');
INSERT INTO TEST VALUES(8, 'Leena');
INSERT INTO TEST VALUES(7, 'Amy');
INSERT INTO TEST VALUES(11, 'Bill');
INSERT INTO TEST VALUES(9, 'Teri');
INSERT INTO TEST VALUES(5, 'Julie');
留意上面蓝色部分
a,) 当ORDER BY字段为PRIMARY KEY时, 此时Oracle是先ORDER BY再对记录ROWNUM
如下SQL:
SQL> SELECT ID, NAME, ROWID FROM TEST WHERE ROWNUM < 6 ORDER BY ID;
ID NAME ROWID
---------- --------------- ------------------
1 John AAADm5AABAAAGJiAAB
2 Julie AAADm5AABAAAGJiAAD
3 Stacy AAADm5AABAAAGJiAAE
4 Tim AAADm5AABAAAGJiAAC
5 Julie AAADm5AABAAAGJiAAK
再看看ID为99的记录的ROWID
SQL> SELECT ID, NAME, ROWID FROM TEST;
ID NAME ROWID
---------- --------------- ------------------
99 John AAADm5AABAAAGJiAAA
1 John AAADm5AABAAAGJiAAB
4 Tim AAADm5AABAAAGJiAAC
2 Julie AAADm5AABAAAGJiAAD
3 Stacy AAADm5AABAAAGJiAAE
6 Rahul AAADm5AABAAAGJiAAF
8 Leena AAADm5AABAAAGJiAAG
7 Amy AAADm5AABAAAGJiAAH
11 Bill AAADm5AABAAAGJiAAI
9 Teri AAADm5AABAAAGJiAAJ
5 Julie AAADm5AABAAAGJiAAK
注意粉红色部分, 应该可以看出ID为99的记录最先INSERT, 其ROWID最小,
但由于其ID最大, 且ID为PRIMARY KEY, 因此在
SELECT ID, NAME, ROWID FROM TEST WHERE ROWNUM < 6 ORDER BY ID
先对ID做了ORDER BY操作, 再对操作后的结果集赋ROWNUM,
然后取出符合条件(ROWNUM < 6)的值
b,) 当ORDER BY字段不为PRIMARY KEY时, 参照我写的第 1 种情况
转自http://www.itpub.net/thread-961972-1-1.html