SqlServer 几种分页方法比较

最新推荐文章于 2023-07-18 16:54:17 发布

行侠者

最新推荐文章于 2023-07-18 16:54:17 发布

阅读量656

点赞数

分类专栏： SQL Server 文章标签： Offset Fetch Next Row_Number SqlServer分页

本文链接：https://blog.csdn.net/feter1992/article/details/45560633

版权

SQL Server 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一. SqlServer 2005及以上版本分页使用 Row_Number()(分页并返回总行数)

Declare @PageSize int,@PageIndex int  
Set @PageSize = 5  
Set @PageIndex = 1  
Select * From (  
    Select Row_Number() over (order by CreateTime Desc) RowNum,Id,Name,Tel,
			[Address],Pwd,Email,Gender,COUNT(*) OVER(PARTITION BY '') AS Total 
	From [dbo].UserInfo With(Nolock) Where Name Like '%%'
) A  
Where  A.RowNum between (((@PageIndex-1)*@PageSize)+1) and (@PageIndex*@PageSize)

二. SqlServer 2012及以上版本分页(分页并返回总行数)

Declare @PageSize int, @PageIndex int  
Set @PageSize = 5
Set @PageIndex = 1
Select Id,Name,Tel,Address,Pwd,Email,Gender,COUNT(*) OVER(PARTITION BY '') AS Total 
From dbo.UserInfo With(Nolock)
Where Name like '%%'
Order by CreateTime Desc
OFFSET (@PageIndex-1)*@PageSize Rows  
FETCH NEXT @PageSize ROWS ONLY;