antshome (original work) first published: CSDN
completes database paging with one statement
select top 10 b.* from (select top 20 primary key field, sorting field from table name order by sorting field desc) a, table name b where b. Primary key field = a. Primary key field order by a. Sorting field
10 = Number of records per page
20 = (Current page + 1) * Number of records per page
or more statements can achieve paging, but the final result taken out is sorted in ascending order. If the result set is required, it is in descending order ( For example, time), there are two ways to deal with it
. 1. Use the following statement, but the efficiency may be lower.
select * from table name b, (select top 10 primary key field, sorting field from (select top 20 primary key field, sorting field from Table name order by sort field desc) a order by sort field) c where b. Primary key field = c. Primary key field order by c. Sort field desc
2. Process in ado, move the record set cursor to the end, and then move it forward
''The following is an asp example
set rsTemp = Server.CreateObject("adodb.recordset")
rsTemp.Open statement,conn,1,1
rsTemp.MoveLast
for i = 1 to rsTemp.RecordCount
'Value....
rsTemp.MovePrevious
next
After testing, the above paging method is faster than using temporary tables and is simple and easy to use.