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 字段) 




下载本文