sql中2中分页 row_number() 和OFFSET, FETCH 分页（2012特有）_飞天海里鱼的博客-CSDN博客

来源： sql中2中分页 row_number() 和OFFSET, FETCH 分页（2012特有）_飞天海里鱼的博客-CSDN博客

第一个种分页，使用row_number()  over(order by)

比如：

CREATE TABLE [dbo].[Student](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL

)

declare @pageIndex int=3
declare @pageSize int=5

select * from

(

select ROW_NUMBER() over(order by ID) as numbers,ID,name from student

)  c where numbers between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize

1. ORDER BY order_by_expression
2. [ COLLATE collation_name ]
3. [ ASC | DESC ]
4. [ ,…n ]
5. [ <offset_fetch> ]
7. <offset_fetch> ::=
8. {
9. OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
10. [
11. FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
12. ]

第二种

select * from student order by ID OFFSET (@pageIndex-1)*@pageSize ROW FETCH next @pageSize rows only

推荐采用第二种