分类: 数据库开发技术
2008-08-28 09:36:44
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.
了解了这个原理,就能知道select * from table where rownum > 2,所有这种 rownum> 不会有查询结果。对此oracle文档中对此也有解释:The first row fetched is assigned aROWNUM
of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM
of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.那么如果有这种需要如何操作呢,后面将进行讨论。针对where后面的条件,rownum是最后满足的。
如果rownum条件之后跟着order by排序,那么结果会示排序的字段为主键还是非主键而定。如果order by后面是主键,那么就会先进行order 不要排序,然后再满足rownum的条件。
如果order by后面是非主键,那么会先满足rownum条件,再order by排序(如果此时想先order by排序,再满足rownum条件,这是就要采用子查询的方法)。
select * from (select * from A order by id) where rownum <
order by主键 > rownum > order by非主键
eg:
select * from t;
ID
----------
1
2
5
3
select rownum,id from t;
ROWNUM ID
---------- ----------
1 1
2 2
3 5
4 3
select rownum,id from t order by id;
ROWNUM ID
---------- ----------
1 1
2 2
4 3
3 5
alter table t add constraint pk_t primary key(id);
表已更改。
select rownum,id from t order by id;
ROWNUM ID
---------- ----------
1 1
2 2
3 3
4 5
看一下创建主键之前和之后的变化就能理解了