Oracle, MYSQL, SQL Server data paging query

Paging Query for sqlserver2005

SQLSERVER2005 has been using the top keyword to achieve paged query, but inefficient, in the sqlserver2005 and later versions of the use of row_number () parsing function to complete the paging query, the efficiency has been greatly improved, but the SQL statement is more complex, The general formula for a paged query is given below:

/** 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;

　　

Four ways of implementing SQL Server paging query

Oracle Paging Query

①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

②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

　　

Paged query for MySQL

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

　　

Oracle, MYSQL, SQL Server data paging query