Oracle分页及实例说明为何要嵌套两层或三层

Oracle分页和MySQL分页有点不一样，MySQL利用limit分页，Oracle利用rownum关键字来进行分页。

–rownum关键字：oracle对外提供的自动给查询结果编号的关键字，与每行的数据没有关系。
–注意：rownum关键字只能做< <=的判断，不能进行> >=的判断，所以外层我们用内层查出的rownum放到外层去比较

接下来就用这张表来尝试分页，这张表一共有31条数据

select * from table t

1.无排序写法

--第几页：page
--每页大小：size

SELECT t2.*,rownum
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM table t
         WHERE ROWNUM <= size*page) t2
WHERE t2.rowno > size*(page-1);

例：

--第几页：page=1
--每页大小：size=10

SELECT t2.*,rownum
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM table t
         WHERE ROWNUM <= 10*1) t2
WHERE t2.rowno > 10*(1-1);

--第几页：page=2
--每页大小：size=20

SELECT t2.*,rownum
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM table t
         WHERE ROWNUM <= 20*2) t2
WHERE t2.rowno > 20*(2-1);

看到上面的查询语句你可能会想到为什么不直接用一层来解决呢
SELECT ROWNUM AS rowno, t.* FROM table t WHERE ROWNUM <= size*page and rownum > size*(page-1);
你会发现明明是有数据的，但用这语句就是查不出来呢
SELECT ROWNUM AS rowno, t.* FROM table t WHERE ROWNUM <= 10*2 and rownum > 10*(2-1);
这样看的可能不是太清晰，那这样呢
SELECT ROWNUM AS rowno, t.* FROM table t WHERE ROWNUM > 10;
是不是发现了端倪，>或者>=对rownum无效，所以一层无法实现分页查询

2.有排序的写法

--第几页：page
--每页大小：size

SELECT t2.*
   FROM (SELECT ROWNUM AS rowno, t1.*
           FROM (SELECT t.* FROM table t
                  order by activity_id desc) t1
          WHERE ROWNUM <= size * page) t2
  WHERE rowno > size * (page - 1);

注意，用到order by的查询需要三层，这里肯定又会有疑问，两层为什么不行，我在内层加上order by排序不行吗，这我们需要考虑到SQL的执行顺序，select是要比order by先执行的，这意味着什么，我们先select了10条数据，然后再对这10条数据进行排序，这样好像不是我们想要的结果，所以为了让order by先执行，那我们就得再嵌套一层来解决这个问题。
例：

--第几页：page
--每页大小：size

SELECT t1.*
   FROM (SELECT ROWNUM AS rowno, t.*
           FROM table t
          WHERE ROWNUM <= 10 * 1
          order by activity_id desc) t1
  WHERE rowno > 10 * (1 - 1);

--第几页：page=1
--每页大小：size=10

SELECT t2.*
   FROM (SELECT ROWNUM AS rowno, t1.*
           FROM (SELECT t.* FROM table t
                  order by activity_id desc) t1
          WHERE ROWNUM <= 10 * 1) t2
  WHERE rowno > 10 * (1 - 1);