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