MSSQL MySQL database paging (Stored Procedure)

Let's take a look at the paging SQL of a single SQL statement.

Method 1:
Applicable to SQL Server 2000/2005Copy codeThe Code is as follows: select top page size *
FROM table1
WHERE id NOT IN
(
Select top page size * (page-1) id FROM table1 order by id
)
Order by id

Method 2:
Applicable to SQL Server 2000/2005Copy codeThe Code is as follows: select top page size *
FROM table1
WHERE id>
(
Select isnull (MAX (id), 0)
FROM
(
Select top page size * (page-1) id FROM table1 order by id
)
)
Order by id

Method 3:
Applicable to SQL Server 2005Copy codeThe Code is as follows: select top page size *
FROM
(
SELECT ROW_NUMBER () OVER (order by id) AS RowNumber, * FROM table1
)
WHERE RowNumber> page size * (page number-1)

Method 4:
Applicable to SQL Server 2005Copy codeThe Code is as follows: row_number () must be set to order by. If this parameter is not specified, the following implementation can be performed, but the correctness of the paging result cannot be ensured because the sorting is not necessarily reliable. The first query record A may be on the first page, and the second query is on the second page.
Declare @ PageNo int, @ pageSize int;
Set @ PageNo = 2
Set @ pageSize = 20
Select * from (
Select row_number () over (order by getdate () rn, * from sys. objects)
Tb where rn> (@ PageNo-1) * @ pageSize and rn <= @ PageNo * @ pageSize

Another way is to use the sorting field as a variable and use dynamic SQL to change it to a stored procedure.Copy codeThe Code is as follows: declare @ PageNo int, @ pageSize int;
Declare @ TableName varchar (128), @ OrderColumns varchar (500), @ SQL varchar (max );
Set @ PageNo = 2
Set @ pageSize = 20
Set @ TableName = 'sys. objects'
Set @ OrderColumns = 'name ASC, object_id DESC'
Set @ SQL = 'select * from (
Select row_number () over (order by '+ @ OrderColumns +') rn, * from '+ @ TableName +') tb where rn> '+ convert (varchar (50 ), (@ PageNo-1) * @ pageSize) + 'and rn <=' + convert (varchar (50), @ PageNo * @ pageSize)
Print @ SQL
Exec (@ SQL)

Method 5: (using SQL cursor Stored Procedure paging)
Applicable to SQL Server 2005Copy codeThe Code is as follows: create procedure SqlPager
@ 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

Method 5: (use MySQL limit)
Applicable to MySQL
Usage of limit in mysql [common data paging]
When we use a query statement, we often need to return the first few or a few rows of data in the middle. What should we do at this time? Don't worry, mysql already provides us with such a function.Copy codeThe Code is as follows: select * from table limit [offset,] rows | rows offset
The limit clause can be used to force the select statement to return the specified number of records. Limit accepts one or two numeric parameters. The parameter must be an integer constant. If two parameters are specified, the first parameter specifies the offset of the first returned record row, and the second parameter specifies the maximum number of returned record rows. The offset of the first record row is 0 rather than 1. To be compatible with postgresql, mysql also supports Syntax: limit # offset #.
Mysql> select * from table limit 5, 10; // retrieves records from 6 to 15 rows.
// To retrieve all record rows from an offset to the end of the record set, you can specify the second parameter-1:
Mysql> select * from table limit 95,-1; // retrieves 96-last records.
// If only one parameter is specified, it indicates the maximum number of record rows returned:
Mysql> select * from table limit 5; // retrieve the first five record rows
// In other words, limit n is equivalent to limit 0, n.
1. select * from tablename <Condition Statement> limit 100,15
15 records are retrieved from the 101-115 records)
2. select * from tablename <Condition Statement> limit 100,-1
Starting from 100th-the last record
3. select * from tablename <Condition Statement> limit 15
Equivalent to limit. Obtain the first 15 data records from the query results.
Note: page size: the number of lines per page; page number: page number. During use, replace "page size" and "page size * (page size-1)" with numbers.

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 a comparison: My conclusion is:
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