Oracle 实现分页

使用三层嵌套正确实现Oracle分页:+++++++++++++++++++++++++++++++++++++++
原始记录：
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 * from
(
select rownum r,t.* from t_stu t
)
where r between 1 and 3 order by s_birthday desc
返回：
R STU_ID S_NAME C_ID S_BIRTHDAY
2 S0002 李四 C01 1989-3-21
3 S0003 张三丰 C02 1983-7-9
1 S0001 张三 C01 1980-1-13
说明：上面的结果可能与你的不一样,因为Oracle在没有order by的情况,是随机选取记录的。前3名的记录应该是：
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
第二种：换用三层嵌套查询：
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.