数据库分页的几种方式

1. 通过数据库的ROW_NUMBER()Over() 函数

eg：

   select * from (select ROW_NUMBER()Over(order by dbkey desc) as rowid,UsersLoginName from PayClerk) B WHERE rowid between 1 and 20

    pagesize*(pagindex-1)   ----- pagesize*(pageindex)

2. 通过offset  x fetch next  (SQLServer2012 或者更高版本)

         select * from TAccount order by DbKey desc offset 0 rows fetch next 20 rows only

3.通过  top  not in

      select top 20 * from TAccount  where dbkey not in (select top 40 dbkey from TAccount  order by dbkey desc) order by dbkey desc

4.通过存储过程

Create PROCEDURE ProAccount
　　@pagesize int,   //页大小
　　@pageindex int   //页索引
AS
　　BEGIN
　　　　declare @currentRowNumberIndex int    //当前分页起始Row_Number
　　　　set @currentRowNumberIndex =@pagesize*(@pageindex-1)+1
　　　　select * from (select ROW_NUMBER()over(order by dbkey desc) as rowid,* from TAccount) B where rowid between @currentRowNumberIndex and @pageindex*@pagesize
　　END
GO

 

转载于:https://www.cnblogs.com/yaoweijun/p/8066691.html