SQLServer数据库分页

以  项目表 PM_Project  为例。

　　PM_Project 全部内容如下(共6条数据)：

 

一、Top – Not In - Top 方式分页

直接的，原始的，不采用函数，纯手动挡。

分步探索过程：

　　采用的最直接做法就是使用两个Top来实现。

DECLARE  @pageSize INT =4,@pageIndex INT =1
    BEGIN
SELECT TOP (@pageSize)
        *
FROM ( SELECT TOP (@pageSize * @pageIndex)
                    *
          FROM    PM_Project
          ORDER BY Id DESC --- 内查询倒序
        ) AS temp
ORDER BY Id                ---外查询正序， 内外查询顺序不一致即可    
    END
    GO

 结果：

 

乍一看是没有问题的，但是仔细一看会发现其中存在的问题。当符合条件的纪录集小于每页记录数时，没有问题，但是当大于就有问题了。

    例如上边代码，实际满足条件的是6条，即全部满足（因为根本就没加条件）。第一页是没有问题的，但是第二页就有问题了。现在共6条数据，每页4条，按理说第二页应该只有2条。但是使用如上的方法，每次都会返回4条记录。

  当pageIndex =2 时，结果如下：

 

 沿用上面的思路，把代码修改为了采用三层查询，最内一层查询所有满足条件的数据，然后第二层选择Top PageSize个所有NOT IN 第一层数据中的数据即可，因为使用了NOT IN所以不存在第一种方法中的bug

 

DECLARE  @pageSize INT =4,@pageIndex INT =2  ---直接看第二页
    BEGIN

SELECT  *
FROM    PM_Project
WHERE   Id IN (
        SELECT TOP ( @pageSize )
                Id
        FROM    PM_Project
        WHERE   Id NOT IN ( SELECT TOP ( @pageSize * (@pageIndex-1) )---去除本页之前的所有id
                                            Id
                                   FROM     PM_Project
                                   ORDER BY Id)
        ORDER BY PM_Project.Id)
ORDER BY PM_Project.Id  ASC

    END
    GO

结果：

 

二、ROW_NUMBER()的方式实现分页

语法：　　

ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

分步过程：

首先查询全部满足条件的数据，并使用 ROW_NUMBER() 函数，可以根据给定好的排序字段规则，为查询结果生成记录序号。

SELECT ROW_NUMBER() OVER (ORDER BY id) rownum,
　　　　*
FROM PM_Project

结果：(共6条数据)

然后用TOP()函数取出一页数据

DECLARE  @pageSize INT =4,@pageIndex INT =1  ------每页4条，第一页
    BEGIN

    SELECT TOP(@pageSize*@pageIndex) 
        ROW_NUMBER() OVER (ORDER BY id) rownum,
        *
    FROM PM_Project
    END
    GO

这样取第一页还是可以的，但是往后就会越来越多。现在将上边的查询作为内查询，再外查询中通过条件来控制获取页数问题。

策略很简单，首先我们选取包含要查页的数据，然后使用ROW_NUMER函数进行编号， 然后在外查询中指定rownum大于页起始记录即可。这种方式简单快捷。

DECLARE  @pageSize INT =4,@pageIndex INT =2  ------每页4条，第二页
    BEGIN
SELECT*FROM
(
    SELECT TOP(@pageSize*@pageIndex) 
    ROW_NUMBER() OVER (ORDER BY id) rownum,*
    FROM PM_Project
)temp
WHERE temp.rownum>(@pageSize*(@pageIndex-1))
    END
    GO

查询第二页的结果：

在 Sql Server 2000 之后的版本中，ROW_NUMBER() 这种分页方式一直都是很不错的，比起之前的游标分页，性能好了很多，因为 ROW_NUMBER() 并不会引起全表扫表，但是，语法比较复杂，并且，随着页码的增加，性能也越来越差。 

 

 

三、使用CTE(common_table_expression,公用表表达式）的方式

  使用很简单，就是把内查询放在CTE 里面。

DECLARE  @pageSize INT =4,@pageIndex INT =2
    BEGIN
WITH temp
          AS ( SELECT TOP ( @pageSize * @pageIndex )
                        ROW_NUMBER() OVER ( ORDER BY id) AS rownum ,
                        *
               FROM PM_Project
             )
SELECT *
FROM    temp
WHERE   temp.rownum > ( @pageSize * ( @pageIndex - 1 ) )
ORDER BY temp.Id 
    END
    GO

结果：

 

四、使用 OFFSET  FETCH 子句分页

语法：

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
 
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

从语法可以看出来 两个方法 后面不但能接 intege 类型的参数，还能接 表达式的，比如  1*2 +3 之类的，同时， Row 或者 Rows 是不区分大小写和单复数的。

 SQL Server 2012及以后版本支持。

例句：

DECLARE  @pageSize INT =4,@pageIndex INT =2
    BEGIN

SELECT*FROM  PM_Project
ORDER BY Id 
OFFSET ( @pageSize * ( @pageIndex - 1 )) ROWS 
FETCH NEXT @pageSize ROWS ONLY

    END
    GO

结果：

 

性能对比：

　　在 Sql Server 2012 里面，分页方法中，Offset and Fetch 同 ROW_NUMBER() 比较起来，无论是性能还是语法，都是有优势的。

　　但是性能方面，优势并不是太大，两者 的 IO 消耗完全相同。

　　只是 在 CPU 方面，Offset and Fetch 方面要好一些，但是不明显。

　　如果对于一个 每秒都要处理成千上万条的分页Sql语句的DB 来说，Offset and Fetch 在CPU 方面的优势会比较明显的，否则，性能的提升并不明显。

 

转载于:https://www.cnblogs.com/dyhao/p/9564030.html