一. 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;