sql几种分页方式（含有存储过程）

*使用not in进行分页

    View Code

    select top  记录条数size  * from Book where id not in ( select top (   页数pageIndex -1 )  记录条数size  id from Book order by id asc ) order by id asc

    那么需要显示所有记录的页数page为：page=（m%n）==0？（m/n）：（m/n+1）；

 

*使用ROW_NUMBER（）进行分页

    View Code 

     select * from (  select ROW_NUMBER() over (order by id asc ) as r1,* from PubBook ) as a where a.r1> (pageIndex -1) size   and a.r1<=pageIndex*size

 

*比较好的存储过程

    View Code

    create PROCEDURE GetPageData

 (

 @TableName varchar(30),--表名称

 @IDName varchar(20),--表主键名称

 @PageIndex int,--当前页数 

 @PageSize int--每页大小 

 )

 AS

 IF @PageIndex > 0

 BEGIN

 set nocount on

    DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)

    SET @PageLowerBound = @PageSize * (@PageIndex-1)

    IF @PageLowerBound<1

     SET @PageLowerBound=1

    SET ROWCOUNT @PageLowerBound

    SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName

      exec sp_executesql @sql,N'@StartID int output',@StartID output

    SET ROWCOUNT 0

    SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '

    EXEC(@sql)

 set nocount off

 END

 

*在没有ROW_NUMBER（）的SQL2000中的分页

CREATE procedure   [dbo].[AspNetPage]

    @tblName         varchar(1000),         -- 表名

    @SelectFieldName    varchar(4000),     -- 要显示的字段名(不要加select)

    @strWhere        varchar(4000),        -- 查询条件(注意: 不要加 where)

    @OrderFieldName     varchar(255),     -- 排序索引字段名

    @PageSize       int ,                  -- 页大小

    @PageIndex      int = 1,               -- 页码

    @iRowCount      int output,            -- 返回记录总数

    @OrderType      bit = 0                -- 设置排序类型, 0 值则升序,1为降序 

           

AS

 declare @strSQL     varchar(4000)        -- 主语句

 declare @strTmp     varchar(4000)         -- 临时变量

 declare @strOrder  varchar(400)         -- 排序类型

 declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句

 --去掉排序字段的空格

 set @OrderFieldName=ltrim(rtrim(@OrderFieldName))

 --如果降序

 if @OrderType != 0

 begin

  set @strTmp = '<(select min'

  set @strOrder = ' order by ' + @OrderFieldName +' desc'

 end

 --如果升序

 else

 begin

  set @strTmp = '>(select max'

  set @strOrder = ' order by ' + @OrderFieldName +' asc'

 end

 --查询主语句

 set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' + @tblName + ' where ' + @OrderFieldName + @strTmp + '('

  + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)

  + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)' + @strOrder

 --如果条件不为空 

 if @strWhere != ''

    set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '  + @tblName + ' where ' + @OrderFieldName + @strTmp + '('

        + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)

        + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

 --如果页面为1

 if @PageIndex = 1

 begin

  set @strTmp = ''

  if @strWhere != ''

   set @strTmp = ' where ' + @strWhere

   set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '  + @tblName + @strTmp + ' ' + @strOrder

 end

 --执行语句

 exec(@strSQL)

 --如果条件不为空

 if @strWhere!=''

 begin

  set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere

 end

 else

 begin

  set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName

 end

 --执行语句

 exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out

GO

-----------------------------------------------------------------------------------------

 

--分页存储过程

Create PROCEDURE [dbo].[UP_CommonPager]

@Sql nvarchar(4000),           -- 要执行的SQL语句

@CurrentPageIndex int = 1,     -- 当前页索引，从1开始编号

@PageSize int = 10,            -- 每页纪录数

@OrderCondition nvarchar(100),       -- 排序规则，如 id,name desc

@RecordCount int output              -- 纪录总数

As

declare @ExecSQL nvarchar(4000),@StartIndex int,@EndIndex int

set @StartIndex = (@CurrentPageIndex-1)*@PageSize+1

set @EndIndex = @StartIndex+@PageSize-1

set @ExecSQL = replace('select @RecordCount=count(*) from (@Sql) UP_CommonPager_Count','@Sql',@Sql)

exec sp_executesql @ExecSQL,N'@RecordCount int output',@RecordCount output

if @RecordCount<=@PageSize

begin

set @ExecSQL = @Sql + ' order by ' + @OrderCondition

exec(@ExecSQL)

end

else

begin

set @ExecSQL = 'with temptbl as (

SELECT ROW_NUMBER() OVER (ORDER BY @OrderCondition)AS RowIndex,UP_CommonPager_TempTbl.* 

from (@Sql) UP_CommonPager_TempTbl)

SELECT * FROM temptbl where RowIndex between @StartIndex and @EndIndex'

set @ExecSQL = replace(@ExecSQL,'@StartIndex',Convert(varchar(10),@StartIndex))

set @ExecSQL = replace(@ExecSQL,'@EndIndex',Convert(varchar(10),@EndIndex))

set @ExecSQL = replace(@ExecSQL,'@OrderCondition',@OrderCondition)

set @ExecSQL = replace(@ExecSQL,'@Sql',@Sql)

--print @ExecSQL

exec(@ExecSQL)

end

GO