Oracle row_number() over()解析函数高效实现分页_Oracle应用_脚
Jun 07, 2016 pm 06:07 PM
oracle
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 ROWNUMORDER 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 字段)
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article
Repo: How To Revive Teammates
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
3 weeks ago
By DDD
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌

Hot tools Tags

Hot Article
Repo: How To Revive Teammates
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
3 weeks ago
By DDD
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Function to calculate the number of days between two dates in oracle

How long will Oracle database logs be kept?

The order of the oracle database startup steps is

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle

How to determine whether two strings are contained in oracle
