select rownum,* from operatedoc where rownum between 22500 and 22520 为什么要在里面嵌套一个查询呢 这样做的结果应该是一样的吧,但效率明显。
比较分页查询语句的优劣 1)Select Rownum,a.* From tableName a Where Rownum<301 Minus Select Rownum,a.* From tableName a Where Rownum<291 and2) Select * From (Select Rownum n,a.* From (Select * From tableName ) a ) Where n>291 And n<301当数据量比较大(约800W的数据) 的时候 1)用了1.602秒 2)用了33.126秒
select * from (Select rownum b,opratedoc.* from opratedoc where b=< 22520) where b>=22500 Order by Status. === b就是rownum,rownum没有大于这个概念呀,就是最近一句。
可能是opratedoc表中字段太多了吧,把“*” 换成需要的字段试试
楼上那句错了,改下: select * from (Select rownum b,opratedoc.* from opratedoc where rownum < 22521) where b>22549 Order by Status
Select * from opratedoc where rownum between 22500 and 22520 Order by Status.
先排序,再取100到200的记录: select * from (select rownum b,t.* from (select * from opratedoc order by Status) t where rownum < 201) where b >100 或者 select * from (select t.*,row_number() over (order by Status) rn from opratedoc t) where rn <201 and rn > 100
先排序,这样子效率太低了, 如, select * from (select * from Opratedoc order by createtime ) where rownum <=200效率远不如,select * from opratedoc rownum<=200 这个事,很不好解决。
select * from opratedoc where rownum <=22500 Minus select * from opratedoc where rownum <22520
查看一下这几种写法的执行计划。我觉得写sql没有什么很固定的哪种写法好。还是要根据实际情况来。
用minus效率应该好些。 如果你的取值范围是排序以后的,那么就得用到嵌套了, Select * From (select * from tableName order by order_column) Where Rownum<301 Minus Select * From (select * from tableName order by order_column) Where Rownum<291
select * from opratedoc where rownum >=22500 and rownum <=22520 Order by Status.
Select * from (Select rownum b,opratedoc.* from opratedoc) where b between 22500 and 22520 Order by Status. 一般情况下这个效率要好点 select * from opratedoc where rowid in ( select rid from ( select rowid as rid, rownum as rn from opratedoc order by status ) where rn between 22500 and 22520 );
where rownum between 22500 and 22520
为什么要在里面嵌套一个查询呢
这样做的结果应该是一样的吧,但效率明显。
1)Select Rownum,a.*
From tableName a
Where Rownum<301
Minus
Select Rownum,a.*
From tableName a
Where Rownum<291
and2) Select * From (Select Rownum n,a.* From (Select * From tableName ) a ) Where n>291 And n<301当数据量比较大(约800W的数据) 的时候
1)用了1.602秒
2)用了33.126秒
where b>=22500 Order by Status.
===
b就是rownum,rownum没有大于这个概念呀,就是最近一句。
select * from (Select rownum b,opratedoc.* from opratedoc where rownum < 22521)
where b>22549 Order by Status
select * from (select rownum b,t.* from (select * from opratedoc order by Status) t where rownum < 201) where b >100
或者
select * from (select t.*,row_number() over (order by Status) rn from opratedoc t) where rn <201 and rn > 100
如,
select * from (select * from Opratedoc order by createtime )
where rownum <=200效率远不如,select * from opratedoc rownum<=200
这个事,很不好解决。
where rownum <=22500
Minus
select * from opratedoc
where rownum <22520
如果你的取值范围是排序以后的,那么就得用到嵌套了,
Select *
From (select * from tableName order by order_column)
Where Rownum<301
Minus
Select *
From (select * from tableName order by order_column)
Where Rownum<291
一般情况下这个效率要好点
select * from opratedoc where rowid in (
select rid from (
select rowid as rid, rownum as rn from opratedoc order by status
)
where rn between 22500 and 22520
);