Database paging Query Method

Here we will mainly explain how to implement paging query in MySQL, SQLServer2000 (and SQLServer2005), and ORCALE databases.

Some people may say that these are available on the Internet, but my main purpose is to summarize these knowledge through my practical application for your convenience.

The following describes how to implement paging query for three databases.

　　I. MySQL database paging Query

The implementation page of MySQL database is relatively simple, and the LIMIT function is provided. Generally, you only need to write the SQL statement directly.

The LIMIT clause can be used to LIMIT the number of data returned by the SELECT statement. It has one or two parameters. If two parameters are provided, the first parameter specifies the position of the first row returned in all data, starting from 0 (note not 1), the second parameter specifies the maximum number of returned rows. For example:

Select * from table WHERE... LIMIT 10; # Return the first 10 rows

Select * from table WHERE... LIMIT; # Return the first 10 rows

Select * from table WHERE... LIMIT 10, 20; # Return the 10-20 rows of data

　　Ii. SQL Server database paging Query

SQL Server databases are divided into SQLServer2000 and SQLServer2005. Generally, a simple method is implemented through the TOP function. As follows:

Select top 10 * FROM SQL WHERE (

Code not in (select top 20 code FROM TestTable order by id ))

ORDER BY ID

Theoretically, the execution time of the entire statement is longer than that of the clause, but the opposite is true. Because 20 records are returned after the clause is executed, and only 10 statements are returned for the entire statement, the biggest factor affecting the database response time is physical I/O operations. One of the most effective ways to restrict physical I/O operations is to use TOP keywords. The TOP keyword is a system-optimized term in SQL SERVER used to extract the first few or the first few percentage data entries.

One of the fatal disadvantages of the preceding statement is that it contains the word "not in" and should be replaced by "not exists" instead of "not in". The execution efficiency of the two statements is actually no different.

IN the preceding paging algorithm, two key factors affect the query speed: TOP and not in. TOP can increase our query speed, while not in will slow down our query speed. Therefore, to increase the speed of our entire paging algorithm, we need to completely transform not in, replace it with other methods.

We know that we can use max (field) or min (field) to extract the maximum or minimum values of almost any field, so if this field is not repeated, then, we can use the max or min of these non-repeated fields as the watershed to make them a reference object for separating each page in the paging algorithm. Here, we can use the operator ">" or "<" to accomplish this mission. For example:

Select top 10 * from table1 where id> 200

The following paging solution is available:

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

The number of executions in this method has never been greatly reduced, and the stamina is still very strong. Especially when the data volume is large, the execution speed of this method will not be reduced at all.

To use TOP, the primary key must be unique and cannot be the Union primary key. If it is a joint primary key, the query results will be out of order.

Currently, SQLServer2005 provides a row_number () function. ROW_NUMBER () is to generate an ordered row number, and the standard of its generation ORDER is the OVER (order by ReportID) followed BY, where ReportID can be a joint primary key. Next, let's take a look at how to apply this RowNo for paging.

Select top 10 * FROM

(

SELECT top 10 ROW_NUMBER () OVER (order by ReportID) AS RowNo

FROM TABLE

) AS

WHERE RowNo> "+ pageIndex * 10

PageIndex is the page number of data we need.

But for SQLServer2000, if it is a joint primary key, I have no solution, if you have any, please contact me. Thank you.

　　Iii. ORCALE database paging Query

You can use the row_number () function or the rownum virtual column in The ORCALE database for paging query.

First: Use the row_number () method of the Analysis Function

Select * from (

Select t. *, row_number () over (order by t1.id) rowno from TABLE1

)

Where rowno between 21 and 40;

Type 2: directly use the rownum virtual Column

Select * from

(Select t. *, rownum as rowno from TABLE1)

Where rowno between 10 and 20

Obviously, the second method is better than the two methods. Because the order by statement is not used, the data retrieval speed will be improved. Especially when the data volume is large, the more obvious the second method is to retrieve data quickly.

Finally, we would like to remind you that the order by page should be used with caution in oracle. Especially in oracle10g, there will be confusion, that is, the same records will appear in different pages.