Query by page in Oracle, DB2, and MySQL

 

Currently, paging technology is becoming more and more common in Web applications. Using database query paging is a highly efficient method,

The paging query statements for Oracle, DB2, and MySQL are listed below.

 

I. Oracle
Select * from (select rownum, name from table where rownum <= endindex)
Where rownum> startindex

Ii. DB2
DB2 paging Query
Select * from (select Field 1, Field 2, Field 3, rownumber () over (column name ASC used for order by sorting) as rn from table name) as A1 where a1.rn between 10 and 20

The above indicates that 10th to 20 records are extracted.

Select * from (select rownumber () over (order by id asc) as rowid from table where rowid <= endindex) as A1
Where a1.rowid> startindex

Iii. MySQL

Select * from Table limit start, pagenum

Select eventid, eventname, createtime, elapsedtime, repeatcount, message, localinstanceid, status, memo from (select rownumber () over (order by createtime ASC) as rowid from ms_event where rowid <= 20) as A1 where a1.rowid> 10

Select * from (select eventid, eventname, createtime, elapsedtime, repeatcount, message, localinstanceid, status, memo,
Rownumber () over (order by createtime ASC) as rn from ms_event) as A1 where a1.rn between 10 and 20

 

 

 

--------------------------------------------------------------------

1. How to use the standard rownum paging query:

Select *
From (select C. *, rownum rn from content c)
Where rn> = 1
And RN <= 5

2. However, if order by addtime is added, the data is incorrectly displayed.

Select *
From (select C. *, rownum rn from content c order by addtime)
Where rn> = 1
And RN <= 5

Solution: add a layer of query to solve the problem,

Select *
From (select rownum RN, T .*
From (select title, addtime from content order by addtime DESC) T)
Where rn> = 1
And RN <= 5

 

If you need to consider the efficiency issue, the above can also be optimized to (the main difference between the two)

Select *
From (select rownum RN, T .*
From (select title, addtime from content order by addtime DESC) T
Where rownum <= 10)
Where rn> = 3

 

----------------------------------------------------------------------

If the query results need to be paged, the efficiency below should be relatively high (pink indicates the location to be filled according to the actual situation)

Efficient paging query methods in three types of databases (currently ):

ORACLE: Select * from (
Select my_table. *, rownum as my_rownum from (
/** Write the actual SQL statement to be queried in parentheses **/
) As mytable where rownum <= 200/** this is the last record on the page **/
/) Where my_rownum> = 10/** here is the first record on the page **/

Sqlserver: Select * from (
Select top page capacity * from (
Select top page capacity * Current page number * from
Table where condition order by field a ASC
) As temptable1 order by field a DESC
) As temptable2 order by field a ASC

MySQL: select statement the first record on the Limit page-1, page capacity