ROWNUM必须从1开始!这是由rownum的特性(没有1就不会有2,没有3……以此类推下去)
<1>第一页数据
SELECT ROWNUM ,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
WHERE ROWNUM between 1 AND 3
结果:
<2>第二页数据
SELECT ROWNUM ,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
WHERE ROWNUM between 4 AND 6
结果:
如何正确显示第二页的数据呢?
有两种方法:
第一种:以上的sql语句再嵌套一个SELECT语句
SELECT k.* FROM (
SELECT ROWNUM r,t.* FROM (
SELECT e.* FROM emp e ORDER BY e.sal DESC ) t
) k
WHERE r between 4 AND 6
结果:
第二种:使用ROW_NUMBER()OVER()函数
select t2.*
from (select t.*, row_number() over(order by t.sal desc) orderNumber
from emp t
) t2
where orderNumber between 4 and 6;
结果: