Sqlserver uses the rank function Row_Number () to query data within a specified range.

In the database, there is a data table Student with the fields StudentID, StudentName, and ClassID. StudentID is not a continuous number. The current task is to query data within the specified range, for example, you want to query the fifth and tenth data records. If StudentID is a continuous number, the task is simpler. For discontinuous numbers,

In the database, there is a data table Student with the fields StudentID, StudentName, and ClassID. StudentID is not a continuous number. The current task is to query data within the specified range, for example, you want to query the fifth and tenth data records. If StudentID is a continuous number, the task is simpler. For discontinuous numbers,

In the database, there is a data table Student with the fields StudentID, StudentName, and ClassID. StudentID is not a continuous number. The current task is to query data within the specified range, for example, you want to query the fifth and tenth data records. If StudentID is a continuous number, the task is simpler. For discontinuous numbers, we can use the Row_Number function to sort the data rows and filter the data based on the generated order number.

The specific steps are as follows:

1. First, use the Row_Number function to sort data.

Select StudentID, StudentName, ClassID, Row_Number () OVER (Order by StudentID) AS 'rownumber'

From Student

Note that the Row_Number function must have the Order By field. The Partition By field is optional. After this step is completed, a column "RowNumber" is added, which is continuous.

2. Use the table generated in step 1 as the base table. The Hong Kong virtual host, combined with the Between... function, can query data within the specified range.

Select *

From (select StudentID, StudentName, ClassID, Hong Kong server lease, Row_Number () OVER (Order by StudentID) AS 'rownumber'

From Student

) As OrderStudent

Where RowNumber between 5 and 10

By performing this step, you can query the fifth to tenth (including the fifth and tenth) data after the ranking.

Based on the above analysis, we can easily expand it. If I want to query the first N data rows of each group: Use the pattition by field of Row_number to group and rank the data, select the data rows in which the ranking of each group is less than N.

The specific SQL statement is:

Select * from (select *, Row_Number () OVER (Partiton By ClassID Order by StudentID) as "RowNumber") as OrderedData

Where RowNumber

Group data tables, rank each group, and host data. Then, select N in each group that is less than the specified number.