One, MySQL database paging query
MySQL database implementation paging is relatively simple, providing a limit function. Generally, you just need to write directly to the SQL statement.
The limit clause can be used to limit the amount of data returned by the SELECT statement. It has one or two arguments, and if two arguments are given, the first parameter specifies the position of the first row returned in all data, starting at 0 (note not 1), and the second parameter specifying a maximum number of rows to return. For example:
SELECT * FROM Table WHERE ... LIMIT 10; #返回前10行
SELECT * FROM Table WHERE ... LIMIT 0, 10; #返回前10行
SELECT * FROM Table WHERE ... LIMIT 10, 20; #返回第10-20 rows of data
Second, SQL Server database paging query
SQL Server database is divided into SQLServer2000 and SQLServer2005. The generally simpler approach is to implement the top function. As follows:
SELECT top * from SQL WHERE (
Code not in (SELECT up code from TestTable ORDER by ID)
ORDER BY ID
This statement, theoretically, the execution time of the entire statement should be longer than the execution time of the clause, but the opposite is true. Because a clause returns 20 records after execution, and the entire statement returns only 10 statements, the most important factor affecting database response time is physical I/O operations. One of the most effective ways to limit physical I/O operations here is to use top keywords. The top keyword is a system-optimized word used in SQL Server to extract the first or previous percentages of data.
One of the fatal drawbacks of the above statement is that it contains not in, and instead of not in, the execution efficiency of the two is virtually indistinguishable.
In the above paging algorithm, there are two key factors that affect our query speed: top and not. Top can improve our query speed, and not in will slow down our query speed, so to improve the speed of our entire paging algorithm, we need to completely transform not in, and replace it with other methods.
We know that in almost any field, we can extract the maximum or minimum value from a field by Max (field) or min (field). So if this field does not repeat, then you can use the max or min of these distinct fields as a watershed, making it a reference point in the paging algorithm that separates each page. Here, we can use the operator ">" or "<" number to complete this mission. Such as:
Select Top * FROM table1 where id>200
So there is the following paging scheme:
Select Top Page Size *
From table1
where id>
(select Max (ID) from
(Select Top ((page 1) * page size) ID from table1 ORDER by ID as T
)
ORDER BY ID
This method of implementation of how many always do not have a big drop, the stamina is still very sufficient. Especially when the data volume is large, the execution speed of this method is not reduced at all.
Using top requires that the primary key must be unique, not a federated primary key. If it is a federated primary key, the results of the query will be scrambled.
Currently, SQLSERVER2005 provides a row_number () function. Row_number () is to generate a sequential line number, and he generates the order of the standard, which is followed by the over (orders by ReportID), where ReportID can be a joint primary key. Below, let's see how to apply this rowno to paging.
SELECT Top * FROM
(
SELECT top Row_number () ReportID as Rowno
From TABLE
) as A
WHERE rowno > "+ pageindex*10
PageIndex is the number of pages we need to have data.
But for SQLServer2000, if the joint primary key, I have no solution, if you have to contact me. Thank you all.
Third, Orcale database paging query
Orcale Database Implementation Paging queries can use the Row_number () function or use the rownum virtual column two methods.
The first: Using analytic function row_number () method
SELECT * FROM (
Select T.*,row_number () over (t1.id) Rowno from TABLE1
)
where rowno between and 40;
The second type: using rownum virtual columns directly
SELECT * FROM
(select T.*,rownum as Rowno from TABLE1)
where Rowno between 20
These two methods are compared, obviously the second method is better. Because the use of the order BY statement, the speed of the retrieval of data, especially the greater the amount of data, the second method of rapid retrieval of data is more obvious.
Finally, let's remind you that Oracle is wary of paging with order by. Especially in oracle10g, there will be confusion, that is, the same record will appear on different pages.
Detailed Source reference: http://www.jb51.net/article/19181.htm
Four: Hsql paging query
Select limit begin length from
Select Limit 0 from tablename
