Orcle分页查询SQL

sql =  SELECT T.* FROM (SELECT X.*, ROWNUM AS RN FROM (SELECT * FROM +表名) X WHERE ROWNUM <= (2*5)) T WHERE T.RN > (1*5);

标椎示例:

SELECT T.*
  FROM (SELECT X.*, ROWNUM AS RN
          FROM (SELECT * FROM DEPT) X
         WHERE ROWNUM <= (2*5)) T
 WHERE T.RN > (1*5);

分步1

查询全部数据

SELECT * FROM DEPT  然后把结果集标示为X

分步2

从X结果集中查询总记录条数

SELECT X.*, ROWNUM AS RN
          FROM (SELECT * FROM DEPT) X
         WHERE ROWNUM <= (2*5)) T

其中 ROWNUM 记录总条数

分步3

限制每页的条数用T表示

SELECT T.*
  FROM (SELECT X.*, ROWNUM AS RN
          FROM (SELECT * FROM DEPT) X
         WHERE ROWNUM <= (2*5)) T
 WHERE T.RN > (1*5);

使用2*5表示当前是第2页

使用1*5标示当前页第一条数据从6开始显示