Paging query for Oracle, MySQL, and sqlserver Databases

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:

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:

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:

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:

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)

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.