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> ]
  6. <offset_fetch> ::=
  7. {
  8. OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
  9. [
  10. FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
  11. ]

第二种

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

推荐采用第二种

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏