Oracle row_number() over()解析函数高效实现分页

create table t_news
(
id number,
n_type varchar2(20),
n_title varchar2(30),
n_count number
)

prompt disabling triggers for t_news...
alter table t_news disable all triggers;
prompt loading t_news...
insert into t_news (id, n_type, n_title, n_count)
values (1, 'it', '爱it1', 100);
insert into t_news (id, n_type, n_title, n_count)
values (2, '体育', '爱体育1', 10);
insert into t_news (id, n_type, n_title, n_count)
values (3, '体育', '爱体育2', 30);
insert into t_news (id, n_type, n_title, n_count)
values (4, 'it', '爱it2', 300);
insert into t_news (id, n_type, n_title, n_count)
values (5, 'it', '爱it3', 200);
insert into t_news (id, n_type, n_title, n_count)
values (6, '体育', '爱体育3', 20);
insert into t_news (id, n_type, n_title, n_count)
values (7, '体育', '爱体育4', 60);
commit;

第一步：我先用rownum

--分页 row_number，不是rownum
--根据n_count从大到小排列，每页3条
select rownum r,t.* from t_news t
where rownum<=3
order by t.n_count desc
--问题：为什么order by以后，行号是乱的？
select rownum r,t.* from t_news t
--原因：先分配了行号，再根据n_count排序

--所以必须排序，再生成行号
select rownum r,t.* from (
select t.* from t_news t order by t.n_count desc ) t

--分页
--err
select rownum r,t.* from (
select t.* from t_news t order by t.n_count desc ) t
where r between 1 and 3

--第1页
select rownum r,t.* from (
select t.* from t_news t order by t.n_count desc ) t
where rownum between 1 and 3

--第2页
select rownum r,t.* from (
select t.* from t_news t order by t.n_count desc ) t
where rownum between 4 and 6
--error: rownum必须从1开始！
select k.* from (
select rownum r,t.* from (
select t.* from t_news t order by t.n_count desc ) t
) k
where r between 4 and 6

--麻烦，效率低！


*****第二步:我用row_number() over()函数
select t2.* from
(select t.*,row_number()over(order by t.n_count desc) ordernumber from t_news t order by t.n_count desc)t2 where ordernumber between 1and 3;


*****************************************************************************************************************************************88
select * from (
select t.*,row_number() over(order by n_count desc) r
from t_news t
order by t.n_count desc
) t
where r between 4 and 6

--通用语法： 解析函数() over(partition by 字段 order by 字段)