建表及初始化SQL
--创建表test
create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);
--创建数据
insert into test values('11','smith','hangzhou','市场',1000);
insert into test values('12','smith','wenzhou','市场',2000);
insert into test values('13','allen','wenzhou','渠道',3000);
insert into test values('14','allen','wenzhou','渠道',4000);
insert into test values('15','jekch','shanghai','渠道',2500);
利用分析函数实现翻页示例
select * from
(
select rownum rn, x.* from
(
select count(1) over (partition by p_), m.* from
(
select 1 p_, r.* from
(
select * from test
) r
) m
) x where rownum<=3
) y where y.rn>=2
以前的实现
-- 获取总行数
select count(*) from
(
select * from test
)
-- 获取目标记录
select * from
(
select rownum rn, x.* from
(
select * from test
) x where rownum<=3
) y where y.rn>=2
利用分析函数相比以前的实现,一句搞定,效率更高些
关于分析函数的介绍:
http://blog.csdn.net/yangzy0808/article/details/5713476