SQL Server sql分页查询

方案1：

select top (@pageSize) * from TableName 
where Id not in(select top ((@pageIndex-1)*@pageSize) Id from TableName order by Id desc) 
order by Id desc

方案2：

select top (@pageSize) * from TableName
 where Id <(select min(Id) from (select top (@pageSize) Id from TableName order by Id desc) as T)
 order by Id desc

方案3：（性能差）

create  procedure Page
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off

sql server 2005版及以上
方案4：

select top (@pageSize) * from (select *,ROW_NUMBER() over(order by Id desc) as rowId from TableName) as a
where a.rowId>(@pageIndex-1)*@pageSize

方案5：

select * from (select *,ROW_NUMBER() over(order by Id desc) as rowId from TableName) as a
where a.rowId>(@pageIndex-1)*@pageSize and a.rowId<@pageIndex*@pageSize

方案6：

select * from (select *,ROW_NUMBER() over(order by Id desc) as rowId from TableName) as a
where a.rowId between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize

 

转载于:https://www.cnblogs.com/DonnyPeng/archive/2013/06/04/3117418.html