oracle row number (),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 字段)

免责声明：本站所有文章和图片均来自用户分享和网络收集，文章和图片版权归原作者及原出处所有，仅供学习与参考，请勿用于商业用途，如果损害了您的权利，请联系网站客服处理。