Oracle,mysql,sqlserver instance of paging query for three kinds of databases _mssql

Mysql:

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

Oracle:

Consider the implementation paging in MySQL, select * from table name limit start record number, show how many, you can achieve our paging effect.

But there are no limit keywords in Oracle, but there are rownum fields

RowNum is a pseudo column that the Oracle system automatically assigns for each row of the query return result, first Act 1, second act 2, etc. ....

First type:

Copy Code code as follows:

SELECT * FROM
(
SELECT a.*, RowNum RN
From (SELECT * from table_name) A
WHERE RowNum <= 40
)
WHERE RN >= 21

The most inner query select * FROM table_name represents the original query statement without paging. RowNum <= 40 and RN >= 21 control the scope of each page of the paging query.

The paging query statement given above is highly efficient in most cases. The purpose of paging is to control the size of the output result set and return the result as quickly as possible. In the paging query above, this consideration is mainly reflected in the sentence where RowNum <= 40.

There are two ways to select the 21st to 40th record, one of which is shown in the above example to control the maximum by RowNum <= 40来 in the second layer of the query, which controls the minimum value at the outermost level of the query. The other way is to remove the query's second-level where rownum <= 40 statement, which controls the minimum and maximum paging values at the outermost edge of the query.

The second type:

Copy Code code as follows:

SELECT * from (select E.*,rownum r from ( select * FROM emp desc) e) E1 where e1.r>21 and E1 .r<=40;

Red: Sort and query all information in descending order of wages.

Brown section: Gets the value of the Red Department query and queries out the rownum of the system and assigns an alias. This sentence is more critical, played a role in the transition, first of all to calculate the rownum to the red part of the specified number, also can be used for the blue outside part of this variable. Specifies the number of start and end records for the query.

Blue section: Specify the record from the beginning to the end of the first few, take out the Brown department value as the query condition variable

Summary: In most cases, the first query is much more efficient than the second.

Sql server:

Paging Scheme one: (using not in and select top pagination)

Statement form:

Copy Code code as follows:

SELECT Top 10 *

From TestTable

WHERE (ID not in

(SELECT Top ID

From TestTable

Order by ID)

ORDER BY ID

SELECT Top Page Size *

From TestTable

WHERE (ID not in

(SELECT top Page size * Pages ID

From table

Order by ID)

ORDER BY ID

Paging Scheme two: (with ID greater than how much and select top pagination)

Statement form:

Copy Code code as follows:

SELECT Top 10 *

From TestTable

WHERE (ID >

(SELECT MAX (ID)

From (SELECT the top ID

From TestTable

Order by ID) as T)

ORDER BY ID

SELECT Top Page Size *

From TestTable

WHERE (ID >

(SELECT MAX (ID)

From (SELECT top page size * Pages ID

From table

Order by ID) as T)

ORDER BY ID

Paging Scenario Three: (using SQL Cursor stored procedure paging)

Copy Code code as follows:

CREATE PROCEDURE Xiaozhengge

@sqlstr nvarchar (4000),--query string

@currentpage int,--nth page

@pagesize INT--Number of rows per page

As

SET NOCOUNT ON

declare @P1 int,--P1 is the ID of the cursor

@rowcount int

EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1, @rowcount = @rowcount output

Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page

Set @currentpage = (@currentpage-1) * @pagesize +1

exec sp_cursorfetch @P1, @currentpage, @pagesize

EXEC sp_cursorclose @P1

SET NOCOUNT OFF

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with a scenario, but it's inefficient.

When optimization is recommended, the query efficiency is increased by adding primary keys and indexes.

Display comparisons through SQL Query Analyzer: The conclusion is:

Paging Scheme two: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements

Paging Scheme one: (Use not in and select top paging) efficiency second, need to stitch SQL statements

Paging Scenario Three: (using SQL Cursor stored procedure paging) is the least efficient, but most versatile

In the actual situation, to be specific analysis.