Copy code code as follows:
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 ', ' Love it1 ', 100);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (2, ' Sports ', ' Love sports 1 ', 10);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (3, ' Sports ', ' Love Sports 2 ', 30);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (4, ' IT ', ' Love It2 ', 300);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (5, ' IT ', ' Love it3 ', 200);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (6, ' Sports ', ' Love Sports 3 ', 20);
Insert into T_news (ID, N_type, N_title, N_count)
VALUES (7, ' Sports ', ' Love sports 4 ', 60);
Commit
The first step: I first use rownum
--pagination row_number, not rownum
--according to N_count from big to small, each page 3
SELECT rownum r,t.* from T_news t
WHERE rownum<=3
ORDER BY T.n_count DESC
--Question: Why is the line number a mess after order?
SELECT rownum r,t.* from T_news t
--reason: First assign line number, then sort by n_count
--so must sort, regenerate the row number
SELECT rownum r,t.* from (
SELECT t.* from T_news t t.n_count DESC) t
--pagination
--err
SELECT rownum r,t.* from (
SELECT t.* from T_news t t.n_count DESC) t
WHERE r between 1 and 3
--Page 1th
SELECT rownum r,t.* from (
SELECT t.* from T_news t t.n_count DESC) t
WHERE rownum between 1 and 3
--Page 2nd
SELECT rownum r,t.* from (
SELECT t.* from T_news t t.n_count DESC) t
WHERE rownum between 4 and 6
--error:rownum must start from 1!
SELECT k.* from (
SELECT rownum r,t.* from (
SELECT t.* from T_news t t.n_count DESC) t
) K
WHERE R BETWEEN 4 and 6
-Trouble, low efficiency!
Step Two: I use the row_number () over () function
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 ordernumb Er between 1and 3;
*************************************************************************************************************** 88
SELECT * FROM (
SELECT T.*,row_number () over (N_count DESC) r
From T_news t
ORDER BY T.n_count DESC
) T
WHERE R BETWEEN 4 and 6
--General Syntax: Parse function () over (partition by field)
