Turn Oracle, MYSQL, SQL Server data paging query

Recently, a simple study of the data paging query of oracle,mysql,sqlserver2005 has been made, and the statements of each query are posted for everyone to learn.

(a), the MySQL paging query

MySQL paging query is the simplest, with the help of keyword limit can be implemented query, query statement general formula:

/** sql: Can be a single-table query statement, or can be a multi-table union query statement * FirstIndex: In fact, the index * PageSize: The number of records displayed per page */select o.* from (SQL) O limit firstindex, PageSize

As shown below, the number of records displayed per page is 20:

Enquiry (1-20) These 20 records

Enquiry (21-40) These 20 records

MySQL's paging query is so simple ...

(b), sqlserver2005 of the paging query

sqlserver2005 has been using the top keyword to achieve paged query, but inefficient, in the sqlserver2005 and later version of the use of row_number () analytic function to complete the paging query, the efficiency has been greatly improved, However, the SQL statements are more complex, and the following is the general formula for paging queries:

/** firstindex: Start index * pageSize: Number per page * ordercolumn: Sorted field name * sql: can be a simple single-table query statement or a complex multi-table union query statement */select top pageSize o.* From (select Row_number () over (order by Ordercolumn) as rownumber,* from (SQL) as O where rownumber>firstindex;

See below, each page shows 20 record number:

Enquiry (1-20) These 20 records

Enquiry (21-40) These 20 records

Knowing the Row_number function in SQL Server, paging is simple .....

(c), Oracle paging query

next focus on Oracle paging query, Oracle's paging query method is relatively more points, ROWNUM, Row_number (), today mainly two more efficient paging query statements.

①rownum Query the general formula of the page:

/** firstindex: Start index * pageSize: Number per page * sql: Can be a simple single-table query statement or a complex multi-table union query statement */select * FROM (select A.*,rownum rn from (SQL) a where rownum<= (firstindex+pagesize)) where Rn>firstindex

The following are the query statements in this manner:

Enquiry (1-21) These 20 records * * * * * * * * (no id=6 records, so the maximum ID of the query is 21)

Query (22-41) These 20 records * * * * * (no id=6 records, so start querying to the ID 22, and the maximum ID is 41)

②row_number () Analytic function paging Query general formula:

/* * FirstIndex: Start index * pageSize: Number per page * ordercolumn: Sorted field name * sql: can be a simple single-table query statement, or a complex multi-table union query statement */select * FROM (SELECT * F Rom (select T.*,row_number () over (order by Ordercolumn) as RowNumber from (SQL) t) p where p.rownumber>firstindex) where Rownum<=pagesize

The following is a paged query effect using the Row_number () method:

Enquiry (1-21) These 20 records * * * * * * * * (no id=6 records, so the maximum ID of the query is 21)

Query (22-41) These 20 records * * * * * (no id=6 records, so start querying to the ID 22, and the maximum ID is 41)

For Oracle's paging queries, these two implementations are chosen because they are both different

First, we know that in the way of ROWNUM query, there is a "where rownum<firstindex+pagesize" in the second layer of SQL statement, according to the principle of Oracle, the second layer of query statements will be embedded in the most internal query, that is to say, The first execution of the query statement resembles the following: select * from Wyuse where rownum< (Firstindex+pagesize) the ORDER by ID ASC, queried from the data table (firstindex+ PageSize) record, so if this value is very small, it will be very efficient, if for a large data volume of the form, this value if it is thousands, such as: SELECT * from Wyuse where rownum< (the) Order by ID ASC, At the outset, 5,000 records will be selected, and the efficiency will be much slower ....

However, compared to the Rownum,row_number () method may be reduced by a layer of nesting, but seemingly for a large number of queries, the efficiency is not high where to go ... However, for large quantities if the table is indexed and then combined row_number () effect will be good (not tested)

This article transferred from: http://www.cnblogs.com/wangyong/p/3396333.html

Turn Oracle, MYSQL, SQL Server data paging query