Database Paging Query Statement database query _ database other

Take a look at the paging SQL of a single SQL statement first.

Method 1:

Applies to SQL Server 2000/2005

Select Top Page Size * FROM table1 WHERE ID: (SELECT top Page Size * (page-1) ID from table1 ORDER by ID

Method 2:

Applies to SQL Server 2000/2005

Select Top Page Size * FROM table1 WHERE ID > select ISNULL (MAX (ID), 0) from (select Top Page Size * (page-1) ID from table1 ID) A) Order by ID

Method 3:

Applies to SQL Server 2005

Select Top Page Size * FROM (select Row_number () over (order by ID) as rownumber,* from table1) A WHERE rownumber > Page Size * (pages -1)

Description, Page size: Number of rows per page; pages: page. When used, replace the page size and page size * (pages-1) with digital.

Mysql

SELECT * FROM TT LIMIT 1,20
select * from TT LIMIT 21,30/*
If you are thousands of thousands of data, directly use MySQL's own function LIMIT normal usage is OK, if it is 1 million The above data, may have to say the method, the following we do a millions data of the paging query statement.
Mysql> SELECT * FROM News where id>= (select IDs from news limit 490000,1) limit 10; 0.18 sec//Obviously, this is the way to win.
Mysql> SELECT * FROM news limit 490000,10//0.22 sec;
*/

The following article mainly describes the MySQL paging the actual operation of the scheme, in fact, the simplest way to achieve MySQL paging is to use the MySQL database LIMIT function, LIMIT [offset,] Rows can retrieve n records from the first m record in the MySQL database table as:

SELECT * FROM table name LIMIT m,n
For example, from the table sys_option (the primary key is sys_id) to retrieve 20 records from the 10th record, the statement reads as follows:

SELECT * from sys_option limit 10,20 
select * FROM table [query condition] ORDER by ID limit?,? 

Oracle

Oracle's paging query can basically follow this article, the next article will be an example to make representations. Here's a brief discussion of multiple-table syndication. For the most common table of magnitude connection queries, the CBO may generally use two ways to connect nested loop and hash join (MERGE join efficiency is less efficient than hash join, the general CBO will not consider). Here, because the paging is used, the maximum number of records to be returned is specified, and the NESTED loop can immediately contain and return the result to the central layer when the number of returning records crosses the maximum, and the hash join must process all the sets (the MERGE join is also). Then, in most cases, selecting nested loop as a query connection method for paging queries has a high efficiency (most of the cases when paging queries are queries on the first few pages of data, the more the number of pages accessed after the less probability).

Therefore, if you don't mind using hint in your system, you can rewrite the paging query to:

Select/*+ first_rows * *
from (
select A.*, rownum RN from
(SELECT * FROM table_name) A
WHERE rownum (=
)
WHERE RN >= 21

Author "ERDP Technical Framework"