Take a look at the paging SQL of a single SQL statement first.
Method 1:
Applies to SQL Server 2000/2005
Copy Code code 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:
Applies to SQL Server 2000/2005
Copy Code code 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
) A
)
ORDER BY ID
Method 3:
Applies to SQL Server 2005
Copy Code code as follows:
SELECT Top Page Size *
From
(
SELECT row_number () over (order by ID) as rownumber,* from table1
) A
WHERE rownumber > Page Size * (pages-1)
Method 4:
Applies to SQL Server 2005
Copy Code code as follows:
Row_number () must make an order by, do not specify can be implemented as follows, but do not guarantee that the paging results are correct, because the ordering is not necessarily reliable. Maybe the first time you query record a on the first page, the second query runs to 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 method is to use the sort field as a variable, through dynamic SQL implementation, can be changed to a stored procedure.
Copy Code code as follows:
declare @PageNo int, @pageSize int;
DECLARE @TableName varchar (128), @OrderColumns varchar, @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 () + @OrderColumns + ') rn,* from ' + @TableName + ') TB where rn > ' +convert (varchar) (@Pa GENO-1) * @pageSize) + ' and RN <= ' +convert (varchar), @PageNo * @pageSize)
Print @SQL
EXEC (@SQL)
Method 5: (Use SQL Cursor stored procedure paging)
Applies to SQL Server 2005
Copy Code code as follows:
CREATE PROCEDURE SqlPager
@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
Method 5: (using MySQL's limit)
Applicable to MySQL
The usage of limit in MySQL [Data paging common]
When we use the query statement, often to return the first few or the middle of a few lines of data, this time how to do? Don't worry, MySQL has provided us with such a feature.
Copy Code code as follows:
SELECT * FROM table limit [offset,] rows | Rows Offset Offset
The limit clause can be used to force a SELECT statement to return the specified number of records. Limit accepts one or two numeric parameters. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first row to return the record, and the second parameter specifies the maximum number of rows to be returned. The offset of the initial record line is 0 (instead of 1): In order to be compatible with PostgreSQL, MySQL also supports syntax: Limit # offset #.
Mysql> select * FROM table limit 5, 10; Retrieve Record Row 6-15
To retrieve all row rows from an offset to the end of a recordset, you can specify a second argument of-1:
Mysql> select * from table limit 95,-1; Retrieves the record row 96-last.
If only one argument is given, it represents the maximum number of record rows returned:
Mysql> select * from table limit 5; Retrieve the first 5 rows of records
In other words, limit n is equivalent to limit 0,n.
1. Select * FROM tablename < conditional statement > limit 100,15
Start with 100 records after 15 (the actual fetch is 第101-115条 data)
2. Select * FROM tablename < conditional statement > limit 100,-1
Start after 100th-record of the last article
3. SELECT * FROM tablename < conditional statement > limit 15
Equivalent to limit 0,15. Query results take the first 15 data
Description, Page size: Number of rows per page; pages: page. When used, replace the page size and page size * (pages-1) with numbers.
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: My 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
