数据库分页的方法

本文将对实现分页的方式作一些探讨：
本文的内容摘自：http://www.cnblogs.com/squirrel_sc/archive/2004/10/02/48583.html
方式一：(利用Not In和SELECT TOP分页)
语句形式：

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

方式二：（利用ID大于多少和SELECT TOP分页）

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

方式三：(利用SQL的游标存储过程分页)

create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off

方式四：AspNetPager的存储过程

CREATE procedure GetNews
     (@pagesize int,
        @pageindex int,
        @docount bit)
        as
        set nocount on
        if(@docount=1)
        select count(id) from news
        else
        begin
        declare @indextable table(id int identity(1,1),nid int)
        declare @PageLowerBound int
        declare @PageUpperBound int
        set @PageLowerBound=(@pageindex-1)*@pagesize
        set @PageUpperBound=@PageLowerBound+@pagesize
        set rowcount @PageUpperBound
        insert into @indextable(nid) select id from news order by addtime desc
        select O.id,O.source,O.title,O.addtime from news O,@indextable t where O.id=t.nid
        and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
        end
        set nocount off
GO

以下摘自：http://tonyqus.cnblogs.com/archive/2006/03/12/344357.html
方式五：行计数

DECLARE @Sort /**//* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT  FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

方式六：游标

DECLARE @PK /**//* PK Type */
DECLARE @tblPK TABLE (
    PK /**//* PK Type */ NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn

OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
    INSERT @tblPK(PK) VALUES(@PK)
    FETCH NEXT FROM PagingCursor INTO @PK
    SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT  FROM Table JOIN @tblPK temp ON Table.PK = temp.PK 
ORDER BY SortColumn

方式二被认为是较好的分页方案；
分页通用的存储过程：摘自http://quitgame.cnblogs.com/archive/2005/09/29/246429.html

CREATE PROCEDURE pagination3

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*',  -- 需要返回的列 

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex  int = 1,           -- 页码

@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回

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

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

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

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

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

 

if @doCount != 0

  begin

    if @strWhere !=''

    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

    else

    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end  

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

 

if @OrderType != 0

begin

    set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0，就执行降序，这句很重要！

end

else

begin

    set @strTmp = ">(select max"

    set @strOrder = " order by [" + @fldName +"] asc"

end

 

if @PageIndex = 1

begin

    if @strWhere != ''   

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder

     else

     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

 

if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end 

end   

exec (@strSQL)

GO