Oracle row_number() over()解析函数高效实现分页
<div class="codetitle"><a style="CURSOR: pointer" data="15418" class="copybut" id="copybut15418" onclick="doCopy('code15418')"> 代码如下:
<div class="codebody" id="code15418">
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_COUNT)
values (2,'体育','爱体育1',10);
insert into T_NEWS (ID,N_COUNT)
values (3,'爱体育2',30);
insert into T_NEWS (ID,N_COUNT)
values (4,'爱it2',300);
insert into T_NEWS (ID,N_COUNT)
values (5,'爱it3',200);
insert into T_NEWS (ID,N_COUNT)
values (6,'爱体育3',20);
insert into T_NEWS (ID,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 字段)
