复制代码 代码如下:
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 字段)