Paging query by SQL statement of Sqlserver

In application development, if the amount of data in the database is too large, you need to do paging processing for the query data, and retrieve the data in the corresponding paging. In the Sqlserver paging statement, there are two common ways: one is when the data table contains a self-increment Id, and paging can be performed according to the size order of the Id; the other is when there is no Id of Int type in the database. In this case, paging needs to be implemented through the Row_Number function.

(1) When there is a self-increment Id in the data table

Suppose the table name we query is Student, and the fields it contains are Id(Id number, self-increasing variable), Name(name), BirthDay(date of birth). Sort by birth date from early to late, get the information of the 31st to 60th students after sorting, and the sentence can be written in the way of Select Top. The details are as follows:

Select Top 30 * FROM Student Where Id Not In (Select Top 30 FROM Student Order By BirthDay) Order By BirthDay

For students ranked 91 to 120, the following sentences are used.

Select Top 30 * FROM Student Where Id Not In (Select Top 90 FROM Student Order By BirthDay) Order By BirthDay

(2) When Id does not exist in data table

In this case, it is impossible to use top to sort. You can use row_Number to sort and then filter the corresponding values.

Change the structure of the Student table above to StudentCode(text type, not number), Name, BirthDay. Sort by birth date from early to late, and obtain the information of the 31st to 60th students after sorting. At this time, the SQL statement is:

With tempTb AS

(

Select ROW_NUMBER() OVER(order by BirthDay ASC) AS RowNumber, FROM Student

)

Select FROM tempTb Where RowNumber>30 AND RowNumber