[SQL] Three Methods of Oracle paging Query

ArticleDirectory

• Use pseudo-column rownum
• Use analysis functions
• Use minus

Use pseudo-column rownum

Query the first 10 records

 
Select * From t_user t where rownum <10;
 

Rank the first three records by student ID

 
Select * from (select ID, realname from t_user order by id asc) Where rownum <= 3
 

Paging SQL statement, which extracts 10 records from 10th records.

 
Select * from (select rownum RN, ID, realname from (select ID, realname from t_user) Where rownum <= 20) T2 where t2.rn> = 10;
 

Based on the student ID ranking, 10 records are extracted starting from 10th records.

 
Select * from (select rownum RN, ID, realname from (select ID, realname from t_user order by id asc) Where rownum <= 20) T2 where t2.rn> = 10;
 

[Note]
1. Where rownum> 1 cannot capture records.
2. Where rownum between 2 and 10 cannot capture records.

Use analysis functions

Use the analysis function row_number () over (order by field)

Rank the first three records by student ID

 
Select * from (select ID, realname, row_number () over (order by id asc) Rn from t_user) Where rn <= 3
 

Based on the student ID ranking, 10 records are extracted starting from 10th records.

 
Select * from (select ID, realname, row_number () over (order by id asc) Rn from t_user) Where rn between 10 and 20
 

Use minus

Extract 10 records from the first 10th records.

 
Select * From t_user where rownum <20 minus select * From t_user where rownum <10;
 

After sorting by ID, 10 records are extracted from the first 10th records.

(Select * from (select * From t_user order by id asc) Where rownum <20) minus (select * from (select * From t_user order by id asc) Where rownum <10 );