SQL Server使用row_number分页的实现方法
本文为大家分享了SQLServer使用row_number分页的实现方法，供大家参考，具体内容如下
1、首先是
selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1
生成带序号的集合
2、再查询该集合的第1 到第5条数据
select*from (selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1)astemp whererowNumberbetween1and5
完整的Sql语句
declare@pagesizeint;declare@pageindexint;set@pagesize=3 set@pageindex=1;--第一页 select*from(selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1)astempwhererowNumberbetween(((@pageindex-1)*@pagesize)+1)and(@pageindex*@pagesize) set@pageindex=2;--第二页 select*from(selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1)astempwhererowNumberbetween(((@pageindex-1)*@pagesize)+1)and(@pageindex*@pagesize) set@pageindex=3;--第三页 select*from(selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1)astempwhererowNumberbetween(((@pageindex-1)*@pagesize)+1)and(@pageindex*@pagesize) set@pageindex=4;--第四页 select*from(selectROW_NUMBER()over(orderbyidasc)as'rowNumber',*fromtable1)astempwhererowNumberbetween(((@pageindex-1)*@pagesize)+1)and(@pageindex*@pagesize)
下面我们来写个存储过程分页
AlterProcedurePagePager
@TableNamevarchar(80),
@Filevarchar(1000),---
@Wherevarchar(500),---带and连接
@OrderFilevarchar(100),--排序字段
@OrderTypevarchar(10),--asc:顺序，desc:倒序
@PageSizevarchar(10),--
@PageIndexvarchar(10)--
as
if(ISNULL(@OrderFile,'')='')
begin
set@OrderFile='ID';
end
if(ISNULL(@OrderType,'')='')
begin
set@OrderType='asc'
end
if(ISNULL(@File,'')='')
begin
set@File='*'
end
declare@selectvarchar(8000)
set@select='select'+@File+'from(select*,ROW_NUMBER()over(orderby'+@OrderFile+''+@OrderType+')as''rowNumber''from'+@TableName+'where1=1'+@Where+')tempwhererowNumberbetween((('+@PageIndex+'-1)*'+@PageSize+')+1)and('+@PageIndex+'*'+@PageSize+')'
exec(@select)
以上就是本文的全部内容，希望对大家学习row_number分页有所帮助。