利用分析函数实现翻页

建表及初始化SQL

Sql代码

--创建表test

create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);

www.2cto.com

--创建数据

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);

利用分析函数实现翻页示例 www.2cto.com

Sql代码

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

以前的实现 www.2cto.com

Sql代码

-- 获取总行数

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

利用分析函数相比以前的实现，一句搞定，效率更高些.