SQL Server使用row_number分页的实现方法
本文为大家分享了SQL Server使用row_number分页的实现方法，供大家参考，具体内容如下
1、首先是
select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1
生成带序号的集合
2、再查询该集合的 第 1 到第 5条数据
select * from
(select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1) as temp
where rowNumber between 1 and 5
完整的Sql语句
declare @pagesize int; declare @pageindex int; set @pagesize = 3
set @pageindex = 1; –第一页
select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)
set @pageindex = 2; –第二页
select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)
set @pageindex = 3; –第三页
select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)
set @pageindex = 4;–第四页
select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber’, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)
下面我们来写个存储过程分页
Alter Procedure PagePager
@TableName varchar(80),
@File varchar(1000),—
@Where varchar(500),—带and连接
@OrderFile varchar(100), — 排序字段
@OrderType varchar(10),–asc:顺序，desc:倒序
@PageSize varchar(10), —
@PageIndex varchar(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 @select varchar(8000)
set @select = ‘ select ‘ + @File + ‘ from ( select *,ROW_NUMBER() over(order by ‘ + @OrderFile + ‘ ‘+ @OrderType + ‘) as ”rowNumber” from ‘ + @TableName + ‘ where 1=1 ‘ + @Where + ‘ ) temp where rowNumber between (((‘ + @PageIndex + ‘ – 1) * ‘ + @PageSize + ‘)+1) and (‘ + @PageIndex + ‘*’+ @PageSize+’)’
exec(@select)
本篇文章到此结束，如果您有相关技术方面疑问可以联系我们技术人员远程解决，感谢大家支持本站！
