今天在写数据库的手动分页的时候,大家应该都碰到过,同时具有order和rownum进行oracle分页排序。
一般的分页肯定是
select
*
from
(
select
a.*, rownum
as
rnum
from
(
select
*
from
yz_bingrenyz) a
where
rownum <=5300)
where
rnum >= 5000
现在分析一下这段代码,rownum相当于一个游标,是sql运行过程中产生的,所以必须在里面的查询中把rownum设置成为一个变量,在外部的查询中对这个变量进行限定才可以,否则是不行的。
错误的代码1:
select
*
from
(
select
a.*, rownum
as
rnum
from
(
select
*
from
yz_bingrenyz) a
where
rownum <=5300)
where
rownum>= 5000
错误的代码2:
select
*
from
(
select
a.*, rownum
as
rnum
from
(
select
*
from
yz_bingrenyz) a
where
rownum <=5300)
where
rownum>= 0
对rownum使用中几种现象的分析说明
有了以上从不同方面建立起来的对rownum的概念,下面认识使用rownum的几种现象:
(1) select rownum,id,age,name from loaddata where rownum != 10 为何是返回前9条数据呢?
为什么它与 select rownum,id,age,name from loaddata where rownum < 10 返回的结果集是一样的?
因为是在查询到结果集后,显示完第9条记录后,之后的记录都是 != 10或者 >=10,所以只显示前面9条记录。
也可以这样理解,rownum为9后,取的记录的rownum为10,因条件为 !=10,所以删掉。然后取下一条,其rownum又是10,也删掉。以此类推。
所以只会显示前面9条记录。
(2)什么rownum >1时查不到一条记录,而 rownum >0或rownum >=1 却总显示所有记录。
这是因为rownum是在查询到的结果集后,再加上去的,它总是从1开始的。
(3)为什么between 1 and 10 或者 between 0 and 10 能查到结果,而用 between 2 and 10 却得不到结果。
原因同上:因为 rownum总是从1开始。
从上可得,任何时候想把rownum = 1这条记录抛弃是不对的。它在结果集中是不可或缺的。
少了rownum=1就像空中楼阁一般不能存在。所以,rownum条件要包含到1。
一般来说,oracle分页方式有:
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from
t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
执行时间0.03秒
2.按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
执行时间1.01秒
3.按ROWNUM来分
select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where
rn>9980;执行时间0.1秒
其中t_xiaoxi为表名称,cid为表的关键字段,取按CID降序排序后的第9981-9999条记录,t_xiaoxi表有70000多条记录
个人感觉1的效率最好,3次之,2最差