SQL分页

有关分页 SQL 的资料很多，有的使用存储过程，有的使用游标。本人不喜欢使用游标，我觉得它耗资、效率低；使用存储过程是个不错的选择，因为存储过程是经过预编译的，执行效率高，也更灵活。先看看单条 SQL 语句的分页 SQL 吧。

方法1：
适用于 SQL Server 2000/2005

SELECT   TOP  页大小  *
FROM  table1
WHERE  id  NOT   IN
          (
           SELECT   TOP  页大小 * (页数 - 1 ) id  FROM  table1  ORDER   BY  id
          )
ORDER   BY  id

方法2：
适用于 SQL Server 2000/2005

SELECT   TOP  页大小  *
FROM  table1
WHERE  id  >
          (
           SELECT   ISNULL ( MAX (id), 0 ) 
          FROM  
                (
                SELECT   TOP  页大小 * (页数 - 1 ) id  FROM  table1  ORDER   BY  id
                )  A
          )
ORDER   BY  id

方法3：
适用于 SQL Server 2005

SELECT   TOP  页大小  *  
FROM  
        (
         SELECT  ROW_NUMBER()  OVER  ( ORDER   BY  id)  AS  RowNumber, *   FROM  table1
        ) A
WHERE  RowNumber  >  页大小 * (页数 - 1 )

说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。 

 

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用

 

 

分页存储过程

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 // 降序(desc)
　　begin
　　set @strTmp = "<(select min"
　　set @strOrder = " order by [" + @fldName +"] desc"
　　--如果@OrderType不是0，就执行降序，这句很重要!
　　end
　　else // 升序(asc)
　　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