本文為大家分享了SQL Server使用row_number分頁的實現(xiàn)方法，供大家參考，具體內(nèi)容如下
1、首先是
select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1
生成帶序號的集合
2、再查詢該集合的 第 1 到第 5條數(shù)據(jù)
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)
以上就是本文的全部內(nèi)容，希望對大家學(xué)習(xí)row_number分頁有所幫助。
您可能感興趣的文章:- SpringMVC4 + MyBatis3 + SQL Server 2014整合教程（含增刪改查分頁）
- SQLSERVER分頁查詢關(guān)于使用Top方式和row_number()解析函數(shù)的不同
- 五種SQL Server分頁存儲過程的方法及性能比較
- SQL Server 分頁查詢通用存儲過程(只做分頁查詢用)
- SQL Server 分頁編號的另一種方式【推薦】
