Instances of paging query for oracle, mysql, and SqlServer Databases

MySql:

The implementation page of MySQL database is relatively simple, and the LIMIT function is provided. Generally, you only need to write the SQL statement directly.
The LIMIT substatement can be used to LIMIT the number of data returned by the SELECT statement. It has one or two parameters. If two parameters are provided, the first parameter specifies the position of the first row returned in all data, starting from 0 (note not 1). The second parameter specifies the maximum number of returned rows. For example:
Select * from table WHERE... LIMIT 10; # Return the first 10 rows
Select * from table WHERE... LIMIT; # Return the first 10 rows
Select * from table WHERE... LIMIT 10, 20; # Return the 10-20 rows of data

 

Oracle:

Considering the implementation paging in mySql, the select * from table name limit starts to show the number of records and how many records can achieve our paging effect.

However, there is no limit keyword in oracle, but the rownum field exists.

Rownum is a pseudo column. It is the number automatically assigned to each row in the query result by the oracle system. The first row is 1, the second row is 2, and so on ....

First:

Copy codeThe Code is as follows:
SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn> = 21

SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.

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

There are two methods to select 21st to 40 records. One is that the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query.

Second:

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: sort by salary in descending order and query all information.

Brown part: obtain the value queried by the red department, query the rownum of the system, and specify the upper alias. This sentence is critical and plays a transitional role. First, you must calculate rownum to specify the sequence number for the red part, or use this variable for the blue part. Specify the number of start records and the condition for ending records.

Blue part: specify the number of records from the beginning to the end, and retrieve the value of the brown Department as the query condition variable.

Conclusion: In most cases, the efficiency of the first query is much higher than that of the second query.

SqlServer:

Paging solution 1: (use Not In and select top pages)

Statement format:

Copy codeThe Code is as follows:
Select top 10 *

FROM TestTable

WHERE (ID NOT IN

(Select top 20 id

FROM TestTable

Order by id ))

ORDER BY ID

 

Select top page size *

FROM TestTable

WHERE (ID NOT IN

(Select top page size * Page id

FROM table

Order by id ))

ORDER BY ID

Paging solution 2: (use the ID greater than the number and select top pages)

Statement format:

Copy codeThe Code is as follows:
Select top 10 *

FROM TestTable

WHERE (ID>

(Select max (id)

FROM (select top 20 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 * Page id

FROM table

Order by id) as t ))

ORDER BY ID

Paging solution 3: (using SQL cursor Stored Procedure paging)

Copy codeThe Code is as follows:
Create procedure XiaoZhengGe

@ Sqlstr nvarchar (4000), -- query string

@ Currentpage int, -- page N

@ Pagesize int -- number of lines per page

As

Set nocount on

Declare @ P1 int, -- P1 is the cursor id

@ Rowcount int

Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output

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

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

Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize

Exec sp_cursorclose @ P1

Set nocount off

Other solutions: If there is no primary key, you can use a temporary table or solution 3, but the efficiency is low.

We recommend that you add primary keys and indexes during optimization to improve query efficiency.

The SQL query Analyzer displays comparison: conclusion:

Paging solution 2: (using more than ID and select top pages) is the most efficient. You need to splice an SQL statement

Paging solution 1: (using Not In and select top pages) The efficiency is second, and SQL statements need to be spliced.

Paging solution 3: (using SQL cursor Stored Procedure paging) The efficiency is the worst, but the most common

In actual situations, specific analysis is required.