oracle基础积累-分页查询sql

场景:
    分页查询sql
环境:
    ORACLE DATABASE 11G; PL/SQL DEVELOPER
1.单表查询分页

SELECT *
  FROM (SELECT ROWNUM AS NUM_ID, A.*
          FROM SENSOR_COLLECT_DATA A
         WHERE ROWNUM <= 3)
 WHERE NUM_ID > 0 ;

2.多表关联分页查询

SELECT *
  FROM (SELECT ROWNUM AS NUM_ID, TTL.*
          FROM (SELECT *
                  FROM SENSOR_COLLECT_DATA A
                  JOIN SENSOR_COLLECT_DATA_B B
                  WHERE  A.SENSOR_ID =  B.SENSOR_ID 
                  AND A.REGION  = B.REGION
                   ) TTL
         WHERE ROWNUM <= 1000)
 WHERE NUM_ID > 0 ;

3.分页效果实现
  比如第一页每一页1000条记录
第一页:

SELECT *
  FROM (SELECT ROWNUM AS NUM_ID, TTL.*
          FROM (SELECT *
                  FROM SENSOR_COLLECT_DATA A
                  JOIN SENSOR_COLLECT_DATA_B B
                  WHERE  A.SENSOR_ID =  B.SENSOR_ID 
                  AND A.REGION  = B.REGION
                   ) TTL
         WHERE ROWNUM <= 1000)
 WHERE NUM_ID > 0 ;

第二页:    

SELECT *
  FROM (SELECT ROWNUM AS NUM_ID, TTL.*
          FROM (SELECT *
                  FROM SENSOR_COLLECT_DATA A
                  JOIN SENSOR_COLLECT_DATA_B B
                  WHERE  A.SENSOR_ID =  B.SENSOR_ID 
                  AND A.REGION  = B.REGION
                   ) TTL
         WHERE ROWNUM <= 2000)
 WHERE NUM_ID > 1000 ;

第N页:

SELECT *
  FROM (SELECT ROWNUM AS NUM_ID, TTL.*
          FROM (SELECT *
                  FROM SENSOR_COLLECT_DATA A
                  JOIN SENSOR_COLLECT_DATA_B B
                  WHERE  A.SENSOR_ID =  B.SENSOR_ID 
                  AND A.REGION  = B.REGION
                   ) TTL
         WHERE ROWNUM <= 1000 * N)
 WHERE NUM_ID > 1000 * (N-1) ;

4.附建表语句

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3),        
  s2       NUMBER(6,3),       
  s3       NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA.s3
  is '传感器采集的值3';

以上,感谢.