Oralce三层嵌套分页详解

原始记录：
select t.*, t.rowid from t_stu t
order by t.s_birthday desc
返回：
STU_ID S_NAME C_ID S_BIRTHDAY
S0002 李四   C01 1989-3-21
S0036 张36   C02 1983-8-10
S0035 张35   C02 1983-8-9
S0034 张34   C02 1983-8-8
S0033 张33   C02 1983-8-7
Oracle分页查询：
三层嵌套查询：
select b.* from
(select rownum r,a.* from
(select t.* from t_stu t order by s_birthday desc )a
)b
where b.r between 1 and 3
返回：
R STU_ID S_NAME C_ID S_BIRTHDAY
1 S0002 李四   C01 1989-3-21
2 S0036 张36   C02 1983-8-10
3 S0035 张35   C02 1983-8-9
正确！
所以Oracle分布查询一定要用三层嵌套,步骤如下：
--第三层：分页过滤
select b.*
  from (
      --第二层：给定行号
    select rownum r,a.* from (
      --第一层：排序
      select * from 表 order by 字段
    ) a
    where rownum<=最大行
)b
where b.r between 最小行 and 最大行

★★★关键点：先排序,后给行号,两个步骤要分开！

为了程序的通用性,对任意数据集都能分页,利用子查询改为如下结构：
--第三层：分页过滤
select b.*
  from (
      --第二层：给定行号
    select rownum r,a.* from (
      --第一层：排序
      select * from (一个已经排序的数据集)
    ) a
    where rownum<=最大行
)b
where b.r between 最小行 and 最大行
如上面的查询改为：
--第三层：分页过滤
select b.*
  from (
      --第二层：给定行号
    select rownum r,a.* from (
      --第一层：排序
      select * from (select t.* from t_stu t order by s_birthday desc)
    ) a
    where rownum<=最大行
)b
where b.r between 最小行 and 最大行
或者其它查询语句：
--第三层：分页过滤
select b.*
  from (
      --第二层：给定行号
    select rownum r,a.* from (
      --第一层：排序
      select * from (select t.* from  新闻表 t order by 发贴日期 desc)
    ) a
    where rownum<=最大行
)b
where b.r between 最小行 and 最大行
★原始数据数据脚本(请在命令窗口中粘贴以下语句即可)：
prompt PL/SQL Developer import file
prompt Created on 2008年8月18日 星期一 by Administrator
set feedback off
set define off
prompt Dropping T_STU...
drop table T_STU cascade constraints;
prompt Creating T_STU...
create table T_STU
(
  STU_ID     CHAR(5),
  S_NAME     CHAR(6),
  C_ID       CHAR(3),
  S_BIRTHDAY DATE,
  S_SEX      CHAR(1)
);
prompt Disabling triggers for T_STU...
alter table T_STU disable all triggers;
prompt Loading T_STU...
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0001', '张三  ', 'C01', to_date('13-01-1980', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0002', '李四  ', 'C01', to_date('21-03-1989', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0003', '张三丰', 'C02', to_date('09-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0004', '张4   ', 'C02', to_date('09-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0005', '张5   ', 'C02', to_date('10-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0006', '张6   ', 'C02', to_date('11-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0007', '张7   ', 'C02', to_date('12-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0008', '张8   ', 'C02', to_date('13-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0009', '张9   ', 'C02', to_date('14-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0010', '张10  ', 'C02', to_date('15-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0011', '张11  ', 'C02', to_date('16-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0012', '张12  ', 'C02', to_date('17-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0013', '张13  ', 'C02', to_date('18-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0014', '张14  ', 'C02', to_date('19-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0015', '张15  ', 'C02', to_date('20-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0016', '张16  ', 'C02', to_date('21-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0017', '张17  ', 'C02', to_date('22-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0018', '张18  ', 'C02', to_date('23-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0019', '张19  ', 'C02', to_date('24-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0020', '张20  ', 'C02', to_date('25-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0021', '张21  ', 'C02', to_date('26-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0022', '张22  ', 'C02', to_date('27-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0023', '张23  ', 'C02', to_date('28-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0024', '张24  ', 'C02', to_date('29-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0025', '张25  ', 'C02', to_date('30-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0026', '张26  ', 'C02', to_date('31-07-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0027', '张27  ', 'C02', to_date('01-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0028', '张28  ', 'C02', to_date('02-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0029', '张29  ', 'C02', to_date('03-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0030', '张30  ', 'C02', to_date('04-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0031', '张31  ', 'C02', to_date('05-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0032', '张32  ', 'C02', to_date('06-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0033', '张33  ', 'C02', to_date('07-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0034', '张34  ', 'C02', to_date('08-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0035', '张35  ', 'C02', to_date('09-08-1983', 'dd-mm-yyyy'), null);
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0036', '张36  ', 'C02', to_date('10-08-1983', 'dd-mm-yyyy'), null);
commit;
prompt 36 records loaded
prompt Enabling triggers for T_STU...
alter table T_STU enable all triggers;
set feedback on
set define on
prompt Done. 

 

也可以使用解析函数完成上面相同的任务,步骤要简单很多,不过要能够理解：
select * from
( select row_number() over(order by s_birthday desc) as r,t.* from t_stu t)
where r between 2 and 3