rownum是一个比较容易混淆的概念,要正确的理解rownum伪列,首先我们要明白它的工作原理.
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工作机制很重要,以下做一些说明:
1 oracle首先执行查询,产生结果集.
2 oracle的rownum值的生成是基于这个结果集的,在产生结果集之后对rownum伪列赋值
需要注意的是:
a. rownum并不是在查询时就对伪列进行赋值了,而是在查询完成之后在数据排序之前赋值的.
b. rownum是在oracle取数据的时候就进行赋值了,也就是在数据排序之前赋值的.
c. rownum赋值是始终是从1开始的,结果集中的rownum的值是从1~n的连续的自然数.这个可以视为oracle对rownum伪列的一个约束
d. rownum伪列不属于任何基表,不能使用基本来引用rownum伪列
3 oracle取得的记录是否满足where子句条件,如果满足则保留结果集中的这条记录,如果不满足就移除结果集中的这条记录,因为rownum要满足伪列的约束,即rownum伪列要满足从1~n连续的自然数约束,所以移除记录后对后续的rownum赋值时,就按1~n连续自然数的规则来赋值,即下一条记录的rownum会被赋予上一条不符合where条件记录的rownum值
4 oracle继续为下一条记录的rownum伪列赋值
5 返回到第三步
从以上的说明中我们可以解释以下sql运行的"怪异"结果.
e.g.
create table STUDENT
(
ID NUMBER(12) not null,
NAME VARCHAR2(20) not null,
AGE NUMBER(3) not null,
GMT_CREATE TIMESTAMP(6) not null,
)
往该表插入20条数据....(省略,自已想象....)
[b]1.select * from STUDENT where rownum > 5[/b]
这条sql返回的结果集是空集.原因是oracle执行查询select * from STUDENT返加结果集之后,对rownum伪列进行赋值.当取得结果集的第一条记录时,给伪列rownum赋值为1,而1并不满足where子句中rownum>5的条件,因此,移除这条记录;然后oracle再取得下一条记录,因为rownum要满足1~n连续自然数的约束,所以oracle令该rownum=1,而这条记录也不满足rownum>5的条件,依次类推,所有记录都不满足条件.因此查询的是空值.
[b]2.select * from STUDENT where rownum != 5[/b] 这条sql返回的结果集包含4条数据.原因是当oracle为第五条记录的rownum赋值时,该rownum==5,不满足rownum!=5的条件,因此移除这条记录,继续为下一条记录的rownum赋值,而rownum必须满足1~n连续自然数的约束,因此这条记录的rownum其实也是5,仍然不满足条件,依次类推,返回的结果集只包含前4条记录
[b]3.select t.rownum from STUDENT t[/b]
这条sql将返回错误,因为rownum是针对结果集赋值的,它是在表查询之后产生的,并不属于表结构的一部分,因此t.rownum是个错误的写法。而t.rowid是没有问题的.
[b]4.select rownum,* from STUDENT t[/b]
这条sql返回错误,因为rownum是针对结果集的,而*是针对结果集的所有列还是表结构STUDENT的所有列呢,这里需要指明*所属的表结构或者视图.select rownum,t.* from STUDENT t是一种正确的写法
[b]5.select rownum,t.* from STUDENT t order by t.AGE desc[/b]
这条sql返回的结果集是按照记录的年龄进行排序的,由于rownum的赋值是在对结果集排序之前完成的,因此把结果集按年龄排序后的记录的rownum可能就不是按自然顺序1~n进行排序的.select rownum,t.* from STUDENT t结果集中rownum是按自然顺序1~n排列的.
[b]6.select rownum,t.* from(select * from STUDENT order by AGE)t[/b]
这条sql返回的结果集的rownum是按自然顺序1~n排列的.
[b]7.select * from
(
select t.*, rownum rn
from (select * from STUDENT) t
where rownum <= 10
)
where rn >= 2[/b]
这条sql是标准的oracle分页查询sql,还有一些非标准的就是不列出来了,呵呵.由于每个视图a都包含它自已的rownum伪列(始终从1开始赋值),因此要使用另一个视图b的rownum伪列就需要为视图b的rownum伪列定义一个别名.最内层的查询由于可能要使用order by子句,因此最好不要把rownum放到最内层的查询中.
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工作机制很重要,以下做一些说明:
1 oracle首先执行查询,产生结果集.
2 oracle的rownum值的生成是基于这个结果集的,在产生结果集之后对rownum伪列赋值
需要注意的是:
a. rownum并不是在查询时就对伪列进行赋值了,而是在查询完成之后在数据排序之前赋值的.
b. rownum是在oracle取数据的时候就进行赋值了,也就是在数据排序之前赋值的.
c. rownum赋值是始终是从1开始的,结果集中的rownum的值是从1~n的连续的自然数.这个可以视为oracle对rownum伪列的一个约束
d. rownum伪列不属于任何基表,不能使用基本来引用rownum伪列
3 oracle取得的记录是否满足where子句条件,如果满足则保留结果集中的这条记录,如果不满足就移除结果集中的这条记录,因为rownum要满足伪列的约束,即rownum伪列要满足从1~n连续的自然数约束,所以移除记录后对后续的rownum赋值时,就按1~n连续自然数的规则来赋值,即下一条记录的rownum会被赋予上一条不符合where条件记录的rownum值
4 oracle继续为下一条记录的rownum伪列赋值
5 返回到第三步
从以上的说明中我们可以解释以下sql运行的"怪异"结果.
e.g.
create table STUDENT
(
ID NUMBER(12) not null,
NAME VARCHAR2(20) not null,
AGE NUMBER(3) not null,
GMT_CREATE TIMESTAMP(6) not null,
)
往该表插入20条数据....(省略,自已想象....)
[b]1.select * from STUDENT where rownum > 5[/b]
这条sql返回的结果集是空集.原因是oracle执行查询select * from STUDENT返加结果集之后,对rownum伪列进行赋值.当取得结果集的第一条记录时,给伪列rownum赋值为1,而1并不满足where子句中rownum>5的条件,因此,移除这条记录;然后oracle再取得下一条记录,因为rownum要满足1~n连续自然数的约束,所以oracle令该rownum=1,而这条记录也不满足rownum>5的条件,依次类推,所有记录都不满足条件.因此查询的是空值.
[b]2.select * from STUDENT where rownum != 5[/b] 这条sql返回的结果集包含4条数据.原因是当oracle为第五条记录的rownum赋值时,该rownum==5,不满足rownum!=5的条件,因此移除这条记录,继续为下一条记录的rownum赋值,而rownum必须满足1~n连续自然数的约束,因此这条记录的rownum其实也是5,仍然不满足条件,依次类推,返回的结果集只包含前4条记录
[b]3.select t.rownum from STUDENT t[/b]
这条sql将返回错误,因为rownum是针对结果集赋值的,它是在表查询之后产生的,并不属于表结构的一部分,因此t.rownum是个错误的写法。而t.rowid是没有问题的.
[b]4.select rownum,* from STUDENT t[/b]
这条sql返回错误,因为rownum是针对结果集的,而*是针对结果集的所有列还是表结构STUDENT的所有列呢,这里需要指明*所属的表结构或者视图.select rownum,t.* from STUDENT t是一种正确的写法
[b]5.select rownum,t.* from STUDENT t order by t.AGE desc[/b]
这条sql返回的结果集是按照记录的年龄进行排序的,由于rownum的赋值是在对结果集排序之前完成的,因此把结果集按年龄排序后的记录的rownum可能就不是按自然顺序1~n进行排序的.select rownum,t.* from STUDENT t结果集中rownum是按自然顺序1~n排列的.
[b]6.select rownum,t.* from(select * from STUDENT order by AGE)t[/b]
这条sql返回的结果集的rownum是按自然顺序1~n排列的.
[b]7.select * from
(
select t.*, rownum rn
from (select * from STUDENT) t
where rownum <= 10
)
where rn >= 2[/b]
这条sql是标准的oracle分页查询sql,还有一些非标准的就是不列出来了,呵呵.由于每个视图a都包含它自已的rownum伪列(始终从1开始赋值),因此要使用另一个视图b的rownum伪列就需要为视图b的rownum伪列定义一个别名.最内层的查询由于可能要使用order by子句,因此最好不要把rownum放到最内层的查询中.