MySql很贴心,有个限制范围查询的limit函数,用起来也很方便,SQL不用嵌套。如下:
select id,name,age,cdate as ctime from emp order by id limit #{start},#{size}
老旧的Oracle用rownum也可以实现类似的功能,只是需要嵌套SQL,用起来不方便,具体如下:
select * from (select rownum no,id,name,age,createdtime as ctime from tb01 where rownum<=10 order by id) tbTmp where no>4
这样把5,6,7,8,9,10六条记录都取出来了。
整句是这样的:
select * from (select rownum no,id,name,age,createdtime as ctime from tb01 where rownum<=10 order by id) tbTmp where no>4
或者:
select * from (select rownum no,id,name,age,createdtime as ctime from tb01 where rownum<=10 order by id) where no>4
Oracle这种方式,还是稍显别扭,希望在其新版本能顺应民心加入limit函数。