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