SQLSERVER 中的几种分页方法

SQLSERVER中的几种分页方法

      这些分页方法虽然不是最优的方案，但你可以当做sql语句练习，熟练掌握sql语句和分页原理， 

当然，文章的最后，我也会给出一种最好的分页方法。

--测试数据库
CREATE TABLE STU
(
   ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
   NAME VARCHAR(40)
)
--插入100一条数据
DECLARE @I INT
SET @I=1
WHILE(@I<=100)
BEGIN
     INSERT STU VALUES('JACK'+CAST( @I AS VARCHAR(3)))
     SET @I=@I+1;
END

需求：(每页显示10条,取出第3页)  PageSize=10 PageIndex=3

方法一：NOT IN 方式 

  SELECT TOP 10 * FROM STU
       WHERE ID NOT IN(
         SELECT TOP 20 ID FROM STU
         ORDER BY ID ASC
       ) ORDER BY ID
        
 --通用表达式
 /*
      SELECT TOP PageSize * FROM TABLENAME
      WHERE  ID NOT IN(
      SELECT TOP (PageIndex-1)*PageSize ID FROM STU
      ORDER BY ID ASC       
     )ORDER BY ID ASC
 */

方法二：row_number 分页

 SELECT * FROM (
     SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWID,* FROM  STU
     ) AS T 
     WHERE T.ROWID BETWEEN 21 AND 30 
     ORDER BY ID
     
  --通用表达式
 /*
     SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS ROWID,* FROM TABLE_NAME
     )AS T
     WHERE T.ROWID BETWEEN (PageSize-1)*PageIndex+1 AND PageSize*PageIndex
     ORDER BY ID
 
 */

 方法二的语句优化（加上了CTE）

WITH CET
AS
(
  SELECT * ,ROW_NUMBER() OVER(ORDER BY ID) AS ROW_ID
  FROM STU
)
SELECT ID,NAME FROM CET 
WHERE ROW_ID BETWEEN 10 AND 20 

方法三:利用ID 大于多少

SELECT TOP 10 * FROM STU WHERE ID>(
     SELECT MAX(ID) FROM (
       SELECT TOP 20 ID FROM STU 
       ORDER BY ID ASC
     )AS T 
 )
 --通用表示式:
 /*
      SELECT TOP PageSize * FROM STU WHERE ID>(
         SELECT MAX(ID) FROM(
            SELECT TOP (PageIndex-1)*PageSize
            ORDER BY ID ASC
         ) AS T
      )
 
 */
   

方法四：asc + desc + top 简称颠倒法把；

SELECT TOP 10 * FROM (
         SELECT TOP 30 * FROM STU ORDER BY ID ASC
)AS T ORDER BY T.ID DESC

-- 通用表达式：
/*
    SELECT TOP PageSize PageSize * FROM (
           SELECT TOP PageSize*PageIndex FROM STU 
           ORDER BY ID ASC
    ) AS T
    ORDER BY T.ID DESC
*/

方法五： IN （可以说值对方法四的一种改进）

    SELECT  TOP 10 * FROM STU WHERE ID IN(
       SELECT TOP 10 ID FROM (
         SELECT TOP 30 ID FROM STU 
         ORDER BY ID ASC
       ) AS T
       ORDER BY T.ID DESC
    )
    /*
      通用表达式：
      
      SELECT TOP PageSize * FROM TABLE WHERE ID IN(
           SELECT TOP PageSize ID FROM (
              SELECT TOP PageSize*PageIndex FROM TABLE
              ORDER BY ID ASC
        )AS T
        ORDER BY T.ID DESC
      )
                 
    */

分页的方法还有很多，如使用游标，和变量表来分页的,
那些效率不是太好，这里就不总结了.

在实际的开发中，不仅仅是分页这个单一的需求，往往掺杂着根据相应的where条件分页
还有可排序的字段 返回记录总条数等等问题，
接下来，我们就做一个完整的存储过程，分页的存储过程，网上有很多的源码，使用的方法也有所不同，有说 row_number 效率高的，有说 top 方法高的，

不管哪种，还有的说，看情况，（具体点看这里：http://www.jb51.net/article/35213.htm）比如：  由此可见在查询页数靠前时，TOP 好一点，页码靠后时row_number好一点，应付百万级的数据都是没问题的，这里，总结一种经过测试的代码(会继续关注这个问题，有更好的代码，我会及时贴出.(总结)....)

(这里，两种方法我都写一下，你也试试吧~)

--第一种 row_number(注意SQL2000不支持row_number()函数,单我相信，应该没有人用sql2000 了 吧)

-- LIUQI 2015 9 28
--测试表 还是 stu
CREATE PROC GETDATA(
     @recordTotal INT OUTPUT,      --输出记录总数
     @tableName   VARCHAR(350),    -- 表名
     @fieldName VARCHAR(300)='*',  --查询字段
     @keyName VARCHAR(200)='ID',   --索引字段
     @pageSize INT  =10,           --每页记录调试
     @pageIndex INT =1,            --当前页
     @orderString varchar(300)='ID',    --排序条件
     @whereString varchar(400)='1=1'     --where条件
     
)AS
BEGIN
     DECLARE @beginRow INT,
             @endRow    INT,
             @tempLimit VARCHAR(200),
             @tempCount NVARCHAR(800),
             @SQL  VARCHAR(800),
             @timeDiff  DATETIME
             
             SET NOCOUNT ON
             SELECT @timeDiff=GETDATE();
             SET @beginRow=(@pageIndex-1)*@pageSize +1
             SET @endRow=@pageIndex*@pageSize;
             SET @tempLimit=' ROWS BETWEEN '+ CAST(@beginRow AS VARCHAR)+' AND '+CAST(@endRow AS VARCHAR);
             
             
             --输出总记录条数；
             --有中观点认为：当集合总数不会有变化时，没有必要每次都输出总记录条数
             --第一次输出之后，应该把它保存起来，以备后是只用
             
             SET @tempCount='SELECT @recordTotal=COUNT(*) FROM ( SELECT '+@keyName+' FROM '+@tableName+' WHERE '+@whereString +') AS MY_TEMP'    
             EXEC SP_EXECUTESQL @tempCount,N'@recordTotal INT OUTPUT ',@recordTotal OUTPUT;
             
             --返回主要的结果结合；
             
             SET @SQL='SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@orderString+') AS ROWS,'+@fieldName+' FROM '+@tableName+' WHERE '+@whereString+') AS MAIN_TEMP WHERE'+@tempLimit                            
                                                
             PRINT @SQL
             EXEC(@SQL)
             SELECT DATEDIFF(MS,@timeDiff,GETDATE()) AS MSTIME
             SET NOCOUNT OFF     

END
GO

--测试;
       DECLARE @TOTAL INT
       EXEC GETDATA @TOTAL OUTPUT,'STU',' ID NAME ',' ID ',10 ,3
       SELECT @TOTAL

        --测试;
       DECLARE @TOTAL INT
       EXEC GETDATA @TOTAL OUTPUT,'STU',' ID NAME ',' ID ',10 ,3
       SELECT @TOTAL
       
 /*
21    21
22    22
23    23
24    24
25    25
26    26
27    27
28    28
29    29
30    30

16 0 (MSTIME) 经过多次测试（本人的是普通pc机器），第一次都在10-16 左右，第二次查询同样的数据时，都是0

100 (TOTALCOUNT)
 */

第二种：TOP

 ALTER PROC GETDATA2
 ( @pageCount      INT OUTPUT,        --页数
   @recordCount    INT OUTPUT,        --总数
   @tableName      VARCHAR(200)='STU',--表名
   @KEY            VARCHAR(100)='ID', --索引字段
   @Fields         VARCHAR(300)='*',  --字段
   @whereStr       VARCHAR(400)='1=1', --where 条件
   @orderStr       VARCHAR(200)='ID',  --排序字段 目前支持 单个字段
   @Begin          INT=0,              --开始位置  
   @pageIndex      INT =1,       
   @pageSize       INT =10
 )
 AS
 BEGIN
       SET NOCOUNT ON
       SET ANSI_WARNINGS ON
       IF (@pageSize<0 OR @pageIndex<0)
       BEGIN 
       RETURN
       END
       
       DECLARE @NEW_WHERE1 VARCHAR(400),
               @NEW_ORDER1 VARCHAR(200),
               @NEW_ORDER2 VARCHAR(200),
               @SQL VARCHAR(800),
               @SQLCOUNT NVARCHAR(800),
               
               @TOP INT
               
               IF(@Begin<=0)
               SET @Begin=0
               ELSE
               SET @Begin=@Begin-1;
               
               --这个部分 可以使用 WHERE 1=1 来进行替换
               IF ISNULL(@whereStr,'')=''
               SET @NEW_WHERE1=''
               ELSE
               SET @NEW_WHERE1=' WHERE ' +@whereStr
               
               IF ISNULL(@orderStr,'')<>''
               BEGIN
                  SET @NEW_ORDER1='ORDER BY '+@orderStr  +' ASC'  
                  SET @NEW_ORDER2=' ORDER BY T.'+@orderStr+' DESC '
               END
               ELSE
               BEGIN
                    SET @NEW_ORDER1=' ORDER BY ID ASC '
                    SET @NEW_ORDER2=' ORDER BY T.ID DESC '
                    
               END
               
               --接下来 获取总记录条数；
               SET @SQLCOUNT=N'SELECT @recordCount=COUNT(1),@pageCount=CEILING((COUNT(1)+0.0)/'+CAST(@pageSize AS VARCHAR )+') FROM '+@tableName+@NEW_WHERE1
               EXEC SP_EXECUTESQL @SQLCOUNT,N'@recordCount INT OUTPUT,@pageCount INT OUTPUT',@recordCount OUTPUT,@pageCount OUTPUT
               
               --如果输入的当期页码大于实际总数；
               --则把实际总页数赋值给当前页数
               IF @pageIndex>CEILING((@recordCount+0.0)/@pageSize)
               BEGIN
                    SET @pageIndex=CEILING((@recordCount+0.0)/@pageSize)
               END
               
               --拼接sql语句；
                SET @SQL='SELECT '+@Fields+' FROM '+@tableName+'  WHERE '+@KEY + ' IN ('
                      +'SELECT TOP '+LTRIM(STR(@pageSize))+' '+@KEY +' FROM '
                      +'( ' 
                      +'SELECT TOP '+LTRIM(STR(@pageSize*@pageIndex+@Begin))+' '+@KEY+' FROM '+@tableName+@NEW_WHERE1+@NEW_ORDER1
                      +') AS T'+@NEW_ORDER2
                      +') '
                PRINT @SQL
       SET NOCOUNT OFF
 END
 
 --测试:
/*
DECLARE @pageCount INT, 
        @recordCount INT
        
EXEC GETDATA2 @pageCount OUTPUT,@recordCount OUTPUT,'STU'
go
*/
 

 第二种，还有点地方，不太合理，需要整理下；.