SQL two pagination offset and row_number simple analysis

Create a new temporary table field ID and insert 1,2,3,4,5,6 into the temporary table

If object_id(' tempdb.. #test ') is not a null drop table #test
CREATE TABLE #test (ID int)
INSERT into #test (ID) VALUES (1), (2), (3), (4), (5), (6)

DECLARE @page int---current page
DECLARE @rows int---page size
Set @page =2
Set @rows =3

---------the current page is 2 and the page size is 3---The result is an ID (4,5,6)--------

(1) Use offset I rows fetch next J rows only mode---i= (@page-1) * @rows, [email protected]
Select T10.id from #test T10
Order by t10.id--requires a sort, otherwise it cannot be paged
Offset (@page-1) * @rows rows FETCH next @rows rows only---offset the current page is zero-based

(2) using the Row_number () function to derive a new sort Lineid, filter the page with between criteria

Select T11.lineid,t11.id from (select cast (Row_number () over (order by ID ASC) as int) Lineid,id from #test) T11
where T11.lineid between (@page-1) * @rows +1 and @rows + (@page-1) * @rows +1
ORDER BY T11.line

The results are as follows:

SQL two pagination offset and row_number simple analysis