sql中2中分页 row_number() 和OFFSET, FETCH 分页（2012特有）

最新推荐文章于 2023-11-08 16:09:23 发布

飞天海里鱼

最新推荐文章于 2023-11-08 16:09:23 发布

阅读量599

点赞数

分类专栏： SqlServer

SqlServer 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

第一个种分页，使用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

ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]   
[ <offset_fetch> ]  
 
<offset_fetch> ::=  
{   
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }  
    [  
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY  
    ]

第二种

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

推荐采用第二种