How to operate MSSQL query data paging

I happened to come across the paging query of data in the past few days. I think it is good. Mark it. There are two methods, both of which use select top. The efficiency will not be discussed here.

Method 1: Use select top to cooperate. not in (or not exists), when querying page n, just filter out the data on page n-1. The example assumes that the number of queries per page is 5, and the data on page 3 is queried;

Select Top 5 UserCode,UserName from userInfo where UserCode not in (select top ((3-1)*5) UserCode from UserInfo order by UserCode asc) order by UserCode asc

before 15 rows of data

Data on the third page

Note that order by must use the same columns and arrangements when querying;

Method 2: Use the built-in function Row_Number() to first add a column of IDs to the query table, and then query the page. It is very simple between ..and...

select UserCode,UserName,PassWord From

(Select UserCode,UserName,PassWord,Rn=Row_Number() OVER(order by UserCode desc) From UserInfo) AS T

Where t.Rn between (3-1)*5 and 3 *5

#Of course, in actual applications, the number of records per page and the page to query can be replaced by parameters.

The above is the detailed content of How to operate MSSQL query data paging. For more information, please follow other related articles on the PHP Chinese website!