原始记录:
select t.*, t.rowid from t_stu t
order by t.s_birthday desc
返回:
STU_ID S_NAME C_ID S_BIRTHDAY
S0002 李四
S0036 张36
S0035 张35
S0034 张34
S0033 张33
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 李四
2 S0036 张36
3 S0035 张35
正确!
所以Oracle分布查询一定要用三层嵌套,步骤如下:
--第三层:分页过滤
select b.*
)b
where b.r between 最小行 and 最大行
★★★关键点:先排序,后给行号,两个步骤要分开!
为了程序的通用性,对任意数据集都能分页,利用子查询改为如下结构:
--第三层:分页过滤
select b.*
)b
where b.r between 最小行 and 最大行
如上面的查询改为:
--第三层:分页过滤
select b.*
)b
where b.r between 最小行 and 最大行
或者其它查询语句:
--第三层:分页过滤
select b.*
)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
(
);
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', '张三
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0002', '李四
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
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0005', '张5
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0006', '张6
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0007', '张7
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0008', '张8
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0009', '张9
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0010', '张10
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0011', '张11
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0012', '张12
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0013', '张13
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0014', '张14
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0015', '张15
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0016', '张16
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0017', '张17
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0018', '张18
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0019', '张19
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0020', '张20
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0021', '张21
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0022', '张22
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0023', '张23
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0024', '张24
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0025', '张25
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0026', '张26
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0027', '张27
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0028', '张28
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0029', '张29
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0030', '张30
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0031', '张31
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0032', '张32
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0033', '张33
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0034', '张34
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0035', '张35
insert into T_STU (STU_ID, S_NAME, C_ID, S_BIRTHDAY, S_SEX)
values ('S0036', '张36
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