select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
跟
select tname,tabtype from (
select tname,tabtype,rownum rn from tab
)
where rownum <= 150 and rn >= 100;
相比,效率和结果上会有区别吗?
oracle 数据分页查询
1. 最好还是利用分析函数
row_number() over ( partition by col1 order by col2 )
比如想取出100-150条记录,按照tname排序
select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;
2. 直接使用rownum 虚列
select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;
使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序.
------------------------------------------------------------------------
经过我的测试,在100万条数据的表中,检索数据的时候,方法2的速度要比方法1要快的.
to bisliu 要什么字段可以自己显示的写出来啊,比如B.COL1,B.COL2。我只是个例子么,本来好的SQL习惯就是不应该用select *的,我们这是列入规范的。
to tf(菜鸟) rownum是虚列,看看OTN的描述你就会明白了:
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM 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.
fuxia的minus 需要查两次数据集,再比对,效率比较底
GerryYang的句子没有order by 可以,不过有order by 的话rownum可是在order by之前就排好的哦,这也是我为什么多写的一层查询。
njcao的句子有一个对rownum的常识性错误哦,呵呵,那样是查不到数据的 :)