Database paging query statement database query and database paging query statement

Database paging query statement database query and database paging query statement

Let's take a look at the paging SQL of a single SQL statement.

Method 1:

Applicable to SQL Server 2000/2005

Select top page size * FROM table1 WHERE id not in (select top page size * (page-1) id FROM table1 order by id) order by id

Method 2:

Applicable 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 order by id) a) order by id

Method 3:

Applicable 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 * (page number-1)

Note: page size: the number of lines per page; page number: page number. During use, replace "page size" and "page size * (page size-1)" with a digital number.

MYSQL

SELECT * from tt limit/* If you have thousands or tens of thousands of data, you can directly use the limit Function of mysql, if it is more than 1 million of the data, it may be necessary to explain the method. Next we will make a paging query statement for millions of data. mysql> select * from news where id> = (select id from news limit 490000, 1) limit 10; // 0.18 sec // obviously, this method wins. mysql> select * from news limit 490000,10 // 0.22 sec ;*/

The following article mainly introduces the actual MySQL paging operation solution. In fact, the simplest way to implement MySQL paging is to use the mysql database LIMIT function, LIMIT [offset,] rows can search for N records starting from the M records in the MySQL database table:

SELECT * FROM table name limit m, N
For example, to retrieve 20 records from the table Sys_option (primary key: sys_id) starting from the first 10th records, the statement is as follows:

Select * from sys_option limit 10, 20 select * from table [query condition] order by id limit ?,?

Oracle

The paging query statement of Oracle can basically follow this article. The next article will explain it through examples. Next we will briefly discuss the situation of Multi-table join. For JOIN queries of the most common equivalent tables, CBO may use two JOIN Methods: nested loop and hash join (merge join is less efficient than hash join, which is not considered by CBO in general ). Because paging is used, the maximum number of records returned is specified. When the number of returned records exceeds the maximum value, the nested loop can immediately curb and return the results to the central layer, however, hash join must process all the replica sets (so does merge join ). In most cases, it is highly efficient to select nested loop as the query connection method for paging queries (most of the time when querying by page is the data of the first few pages, the lower the page number, the lower the access probability ).

Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement:

SELECT /*+ FIRST_ROWS */ * FROM(SELECT A.*, ROWNUM RNFROM (SELECT * FROM TABLE_NAME) AWHERE ROWNUM <= 40)WHERE RN >= 21

Author: ERDP Technical Architecture"

How to write the database paging query statement?

Select top page size *
From table1
Where id>
(Select max (id) from
(Select top (page number-1) * page size) id from table1 order by id) as T
)
Order by id

Access Database paging query, efficiency SQL statement

The efficiency of in is too low and indexes cannot be used. We recommend that you:
Select top page quantity * from table where id> (select top 1 max (id) from (select top (page number-1) * from Table order by id per page volume, name) or
Select top page quantity * from table where id <(in order and Reverse Order)