在oracle中限制返回结果集的大小 select a from (select rownum myrow,a from tt) where myrow between 10 and 20; 经过长期检验,效率很好,大数据量时也很快
select * from table where rownum <=20 minus select * from table where rownum <=10 或者 select * from (select rownum num,table.* from table where rownum <=20) where num >= 10; 都可以,效果好象差不多.
用分析函数ROW_NUMBER() OVER (ORDER BY ...) 也可以。
楼上的几种方法都可以。 怎么说最快呢,个人认为 select a from (select rownum myrow,a from tt) where myrow between 10 and 20; 好用。
不要用between这种东西,影响速度
select * from (select rownum num,table.* from table where rownum <=20) where num >= 10;这个吧
另外,如果数据量超过1000w,推荐: select * from table1 where rowid in( select rowid from (select rownum num,a.rowid from table1 a where rownum <=20) where num >= 10)
select a from (select rownum myrow,a from tt) where myrow between 10 and 20;
经过长期检验,效率很好,大数据量时也很快
minus
select * from table where rownum <=10
或者
select * from (select rownum num,table.* from table where rownum <=20) where num >= 10;
都可以,效果好象差不多.
怎么说最快呢,个人认为
select a from (select rownum myrow,a from tt) where myrow between 10 and 20;
好用。
select * from table1 where rowid in(
select rowid from (select rownum num,a.rowid from table1 a where rownum <=20) where num >= 10)