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分页有所帮助。