Oracle,mysql,sqlserver paging query of three kinds of databases

Mysql:

MySQL database implementation paging is relatively simple, providing the limit function. It is generally only necessary to write directly to the SQL statement immediately after the line.
The limit clause can be used to limit the number of data returned by the SELECT statement, which has one or two parameters, if two arguments are given, the first parameter specifies the position of the first row returned in all data, starting with 0 (note not 1), and the second parameter specifies the 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 bars, you can achieve our paging effect.

However, there is no limit keyword in Oracle, but there are rownum fields

RowNum is a pseudo-column that is assigned by the Oracle system automatically for each row of the query that returns results, first behavior 1, second behavior 2, and so on ....

The first type:

Copy CodeThe code is as follows:
SELECT * FROM
(
SELECT a.*, ROWNUM RN
From (SELECT * from table_name) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

The most internal query, select * FROM table_name, represents the original query statement without paging. ROWNUM <= 40 and RN >= 21 control the range of pages per page for paged queries.

The paging query statement given above has high efficiency in most cases. The purpose of paging is to control the output result set size and return the results as soon as possible. In the above paged query statement, this consideration is mainly reflected in the where ROWNUM <= 40 sentence.

There are two ways to select the 21st to 40th record, one of which is shown in the above example in the second layer of the query through the rownum <= 40来 control The maximum value, at the outermost level of the query control the minimum value. The other way is to remove the ROWNUM <= 40 statement that queries the second layer, controlling the minimum and maximum paging values at the outermost level of the query.

The second type:

Copy CodeThe code is as follows:
SELECT * from (select E.*,rownum r from(SELECT * from emp ORDER BY sal Desc)e) E1 where e1.r>21 and e1.r<=40;

Red section: Sort by salary descending and query all information.

Brown part: Get the value of the Red Department query, and query the rownum of the system and specify the alias. This sentence is more critical, plays a role in the transition, first of all to calculate the rownum to the red part of the specified number, you can also be used for the blue outer part of the variable. Specifies the number of start records on the query and the criteria for ending the record.

Blue section: Specifies the record from the beginning of the first to the end of the first, remove the value of the Brown department as a query condition of the variable

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

Sql server:

Paging Scenario One: (using not in and select top paging)

Statement form:

Copy CodeThe code is 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 * Number of pages ID

From table

ORDER by ID))

ORDER by ID

Paging Scenario Two: (using ID greater than how much and select top paging)

Statement form:

Copy CodeThe code is as follows:
SELECT TOP 10 *

From TestTable

WHERE (ID >

(SELECT MAX (ID)

From (SELECT 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 * Number of pages ID

From table

ORDER by ID) (as T))

ORDER by ID

Paging Scenario Three: (Paging with SQL cursor stored procedures)

Copy CodeThe code is as follows:
CREATE PROCEDURE Xiaozhengge

@sqlstr nvarchar (4000),--query string

@currentpage int,--page n

@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,@[email protected] 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 scenario three, but the efficiency will be low.

When tuning is recommended, the query efficiency increases with the primary key and index.

With SQL Query Analyzer, the comparison is displayed: The conclusion is:

Paging Scenario Two: (using ID greater than how much and select top paging) The most efficient, need to splice SQL statements

Paging Scenario One: (using not and select top paging) second, the need to splice SQL statements

Paging Scenario Three: (Paging with SQL cursor stored procedures) is the least efficient, but the most common

In the actual situation, to be specific analysis.

Oracle,mysql,sqlserver paging query of three kinds of databases