高效分页存储过程[转]

 

代码

/* ********************************************************************************
*      Copyright (C) 2010 www.cnblogs.com/immensity,All Rights Reserved             *
*      Function:  PROC_Pagination                                                 *
*      Description:                                                              *
*             高效率通用分页存储过程                                             *
*      Author:                                                                   *
*             张盼(ZP)                                                           *
*             immensitybiz@163.com                                               *
*             http://www.cnblogs.com/immensity                                   *
*      Finish DateTime:                                                          *
*             2010/04/08                                                         *
*      History:                                                                     *
*             2008/02/07                                                         *         
*       Example:                                                                     *
*              EXEC PROC_Pagination @TableName='表名',@Orderfld='排序列名'        *           
******************************************************************************** */
CREATE   PROCEDURE   [ dbo ] . [ PROC_Pagination ]
(
     @TableName          VARCHAR ( 50 ),             -- 表名
     @Where              VARCHAR ( 1000 )  =   '' ,         -- 查询条件
     @ReturnFields      VARCHAR ( 500 )  =   ' * ' ,         -- 返回的列
     @Orderfld          VARCHAR ( 100 ),             -- 排序的列(多页以,分隔)
     @OrderType          BIT   =  true,                 -- 排序模式(默认为true,表示降序)
     @FKColumn          VARCHAR ( 50 )  =   '' ,         -- 主键列
     @PageIndex          INT   =   1 ,                 -- 行索引
     @PageSize          INT      =   10                  -- 页大小
)
AS
BEGIN
     -- 当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1)  开始
     IF ( @PageIndex < 1 )
         SET   @PageIndex = 1
     ELSE
         SET   @PageIndex = @PageIndex - 1
     -- 当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1)  结束
     DECLARE    @SQL   NVARCHAR ( 2000 ) -- 动态SQL语句
     DECLARE   @OrderColumnCrux   VARCHAR ( 50 ) -- 排序核心  值如下:>(SELECT MAX( 或 <(SELECT MIN(
     SET   @OrderColumnCrux = ' <(SELECT MIN( ' -- 默认为降序模式
     SET   @ReturnFields = '   ' + @ReturnFields + '   ' -- 避免错误  在查询的列的前后加一个空格
     DECLARE   @OrderBy   NVARCHAR ( 255 )             -- 排序
     DECLARE   @RowCount   NVARCHAR ( 1000 ) -- 拼接查询行数的SQL语句

     -- 处理排序开始
     IF ( @Orderfld   IS   NOT   NULL   AND   @Orderfld <> '' )
     BEGIN

         -- 降序
         IF ( @OrderType = 1 )
         BEGIN
             SET   @OrderBy = '  ORDER BY  '   +   REPLACE ( @Orderfld , ' , ' , '  DESC, ' )  +   '  DESC  '
             SET   @OrderColumnCrux = ' <(SELECT MIN( '
         END
         ELSE -- 否则为降序
         BEGIN
             SET   @OrderBy   =   '  ORDER BY  '   +   REPLACE ( @Orderfld , ' , ' , '  ASC, ' )  +   '  ASC  '
             SET   @OrderColumnCrux = ' >(SELECT MAX( '
         END
     END     

     -- 当无主键时候(情况一)
     IF ( @FKColumn   IS   NULL   OR   @FKColumn   =   '' )
     BEGIN
         -- 当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1)  开始
         IF ( @PageIndex <= 0 )
             SET   @PageIndex = 1
         ELSE
             SET   @PageIndex = @PageIndex + 1
         -- 当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1)  结束
         DECLARE   @TotalRecord   int                  -- 记录总数
         DECLARE   @TotalPage   int                      -- 页总数
         DECLARE   @CurrentPageSize   int              -- 当前页面数据数量
         DECLARE   @TotalRecordForPageIndex   int      
         DECLARE   @CutOrderBy   nvarchar ( 255 )    
         DECLARE   @CurrentWhere   nvarchar ( 1000 ) -- 当前情况下的where条件
         -- 降序
         IF   @OrderType   =   1
         BEGIN
             SET   @CutOrderBy   =   '  Order by  ' +   REPLACE ( @Orderfld , ' , ' , '  asc, ' )  +   '  asc  '
         END
         ELSE
         BEGIN
             SET   @CutOrderBy   =   '  Order by  ' +   REPLACE ( @Orderfld , ' , ' , '  desc, ' )  +   '  desc  '     
         END
         -- 计算
         SET   @TotalPage = ( @TotalRecord - 1 ) / @PageSize + 1
         SET   @CurrentPageSize = @PageSize
             IF ( @TotalPage = @PageIndex )
             BEGIN
                 SET   @CurrentPageSize = @TotalRecord % @PageSize
                 IF ( @CurrentPageSize = 0 )
                     SET   @CurrentPageSize = @PageSize
             END
         --  返回记录
         set   @TotalRecordForPageIndex = @PageIndex * @PageSize
         -- 查询条件
         SET   @CurrentWhere = ''
         IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' )
         BEGIN
             SET   @CurrentWhere = '  WHERE 1=1 AND  ' + @Where
         END
         SET   @SQL = ' SELECT * FROM
        (SELECT TOP  ' + STR ( @CurrentPageSize ) + '  * FROM
            (SELECT TOP  ' + STR ( @TotalRecordForPageIndex ) + '   ' + @ReturnFields + '
            FROM  ' + @TableName + '   ' + @CurrentWhere + '   ' + @OrderBy + ' ) TB2
         ' + @CutOrderBy + ' ) TB3
               ' + @OrderBy
         -- 执行查询
         EXEC  SP_EXECUTESQL  @SQL
         -- 为查询表行数准备数据
         IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' )
         BEGIN
             SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName + '  WHERE 1=1 AND  ' + @Where
         END
         ELSE
         BEGIN
             SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName
         END
         EXEC  SP_EXECUTESQL  @RowCount -- 执行查询   得到表行总数
     END
     -- 主键与排序字段相同(情况二)
     ELSE   IF ( @Orderfld = @FKColumn )
     BEGIN         
         IF ( @PageIndex   IS   NOT   NULL   AND   @PageIndex <> 0 )
         BEGIN
             -- 当查询条件不为空
             IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' )
             BEGIN
                 -- 处理SQL语句
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '  WHERE( ' + @FKColumn + @OrderColumnCrux + @FKColumn + ' ) FROM (SELECT TOP  ' + Convert ( varchar ( 5 ),( @PageIndex * @PageSize ))
                 + '   ' + @FKColumn + '  FROM  ' + @TableName + '  WHERE 1=1 AND  ' + @Where + '   ' + @OrderBy + ' ) AS T)) AND  ' + @Where + '   ' + @OrderBy
             END
             ELSE
             BEGIN
                 -- 处理SQL语句
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '  WHERE( ' + @FKColumn + @OrderColumnCrux + @FKColumn + ' ) FROM (SELECT TOP  ' + Convert ( varchar ( 5 ),( @PageIndex * @PageSize ))
                 + '   ' + @FKColumn + '  FROM  ' + @TableName + '   ' + @OrderBy + ' ) AS T))  ' + @OrderBy
             END     
         END
         ELSE
         BEGIN
             IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' ) -- 当查询条件不为空
             BEGIN
                 -- 处理SQL语句
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '  WHERE 1=1 AND  ' + @Where + '   ' + @OrderBy
             END
             ELSE
             BEGIN
                 -- 处理SQL语句
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '   ' + @OrderBy
             END
         END
         EXEC  SP_EXECUTESQL  @SQL -- 执行查询

         -- 为查询表行数准备数据
         IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' )
         BEGIN
             SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName + '  WHERE 1=1 AND  ' + @Where
         END
         ELSE
         BEGIN
             SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName
         END
         EXEC  SP_EXECUTESQL  @RowCount -- 执行查询   得到表行总数
     END
     -- 主键与排序字段不同(情况三)
     ELSE
     BEGIN
         IF ( @Where   IS   NOT   NULL   AND   @Where   <> '' ) -- 当查询条件不为空
                 BEGIN
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '  WHERE  ' + @FKColumn + '  NOT IN '
                 + '  (SELECT TOP  ' + Convert ( varchar ( 5 ),( @PageIndex * @PageSize )) + '   ' + @FKColumn + '  FROM  ' + @TableName
                 + '  WHERE 1=1 AND  ' + @Where + '   ' + @OrderBy + ' ) AND  ' + @Where + '   ' + @OrderBy
                 EXEC  SP_EXECUTESQL  @SQL -- 执行查询
                 SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName + '  WHERE 1=1 AND  ' + @Where
                 EXEC  SP_EXECUTESQL  @RowCount -- 执行查询   得到表行总数
         END
         ELSE
         BEGIN
                 SET   @SQL = ' SELECT TOP  ' + Convert ( varchar ( 5 ), @PageSize ) + '   ' + @ReturnFields + '  FROM  ' + @TableName
                 + '  WHERE  ' + @FKColumn + '  NOT IN '
                 + '  (SELECT TOP  ' + Convert ( varchar ( 5 ),( @PageIndex * @PageSize )) + '   ' + @FKColumn + '  FROM  ' + @TableName
                 + '   ' + @OrderBy + ' )  ' + @OrderBy
                 EXEC  SP_EXECUTESQL  @SQL -- 执行查询
                 SET   @RowCount = ' SELECT COUNT(*) FROM  ' + @TableName
                 EXEC  SP_EXECUTESQL  @RowCount -- 执行查询   得到表行总数
         END
     END
END

 

 

此存储过程可产生3形式中SQL语句

形式一(主要实现对无主键表的分页,可以任意字段排序,但是效率很低)

exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2

SELECT   *   FROM  ( SELECT   TOP   10   *   FROM
( SELECT   TOP   20    *   FROM  articles  ORDER   BY  articleid  ASC  ) TB2
Order   by  articleid  desc  ) TB3
ORDER   BY  articleid  ASC  

 

 

形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)

exec [PROC_Pagination] 'articles',@Orderfld='Title',@PageIndex=2,@FKColumn='articleid'

SELECT   TOP   10    *    FROM  articles  WHERE  articleid  NOT   IN  
( SELECT   TOP   10  articleid  FROM  articles   ORDER   BY  articleid  ASC  )  
ORDER   BY  articleid  ASC  

 

 

形式三(适合以唯一主键排序且数据量很大时)

exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2,@FKColumn='articleid'

SELECT   TOP   10    *    FROM  articles  WHERE (articleid > ( SELECT   MAX (articleid) 
FROM  ( SELECT   TOP   10  articleid  FROM  articles   ORDER   BY  articleid  ASC  )  AS  T))  
ORDER   BY  articleid  ASC  

转自：http://www.cnblogs.com/immensity

转载于:https://www.cnblogs.com/guoqiao/archive/2010/04/08/1707733.html