[转载]几种SQL分页的总结 - 二炮哥 - 博客园

[转载]几种SQL分页的总结 – 二炮哥 – 博客园.

在实际开发的过程中如果记录数非常的庞大，如果直接用SQL语句查询并填充到DataTable中，将是一件非常恐怖的事情。而且对网站性能，服务器性能消耗很大。

两个常犯的错误：

1）在现实数据时，查询时会将所有的满足条件的数据全部填充到DataTable中，然后在程序中根据条件显示其中的一部分数据。

2）在统计数据时，在获取符合条件的记录条数时也是通过将所有满足条件的数据全部填充到DataTable中，然后通过DataTable实例Rows.Count属性来获取记录条数。

这样做的结果就是效率极低，如果数据量太大，可能造成自己需要的数据长久的无法显示，所以显示数据是应该使用分页查询。分页查询就是每次只返回所需要的数据，而不用每次都从数据库中把数据全部提出来，这样可以降低程序与数据库之间的数据传送量，并且还可以提高程序的性能。

一般来说，在数据量大的情况下要分页显示，这样决定返回的查询结果集的参数有两个：当前显示的页数pageIndex和每页显示的记录条数size。

所使用的数据表如下图：表中的id是主键。

下面我们按照id正序排列查询所有人的信息，SQL语句如下：

现在我们对数据进行分页，分页规则就是，页容量5条数据，那么我们完成第1页的数据查询可以使用SQL语句是：

这样我们就可以取出第1页所要显示的5条数据。但是我们应该怎么样编写SQL语句才可以显示第2,3,4……页的数据呢？

如果说我们第1页取出的数据时第1-5条记录，那么第2页的数据就应该是6-10的记录。我们应该怎么做到呢？有两种方法：第一种就是一次性的将所 有的数据都提取出填充到DataTable中，然后在for循环中通过i从5开始，并且i小于10这种方法显示数据，这种方法的缺点前面已经介绍。第二种 方法就是在数据库中对数据进行过滤，这个时候SQL语句中的 not in 就可以很好地排上用场。

那么怎么使用not in将第2页的数据显示出来呢，SQL语句如下：

1 select top 5 * from T_Person where id not in
2 (
3 select top 5 id from T_Person order by id asc
4 )order by id asc;

在这里使用了一个子查询先将第1页的数据编号显示出来，然后使用not in 将1-5的记录从数据中排出，显示的就是6-10 的数据记录。

因为数据id是从1开始，所以id为1-5的记录显示在第1页，id为6-10的记录在第2页显示，id为11-15的记录在第3页显示，依此类推第n页的数据的SQL语句是：n为定义显示数据的第几页，

select top 5 * from T_Person where id not in
(
select top (n-1)5 id from T_Person order by id asc
)order by id asc;

这样就可以根据参数n显示第几页的数据。

还有一个比较重要的知识点就是如何计算数据页分页的总数，如果现在有20条数据，如果每页5条数据，很明显就是分4页。但是如果记录数是21条，这个时候很明显应该分5页。有一个公式，假如总共有m条数据，每页显示n条数据（m，n都大于0），那么需要显示所有记录的页数page为：page=（m%n）==0？（m/n）：（m/n+1）；

第二种分页的方法：

这里需要使用SQL中的ROW_NUMBER函数，该函数的作用就是在返回的记录集合内为每一条记录标上顺序编号。

因为我们要对数据库进行删除操作的话，数据库表中的id的值可能是不连续的。因为在上面的方法中，我们主要是使用id来排序，不需要太多的操作，但是下面的方法，就需要有一个连续的id值来查询数据。

先看一个SQL语句：

select * from
(
select * from T_Person
)as a
where id>5 and id(n-1)*5 and id<=n*5

这个时候我们就可以根据参数n来选出相应页数的数据，但是我们可以很快的发现，这个分页对id的连续有很大的依赖性，所以，我们在对数 据进行删除操作的时候，数据库中的id很可能是不连续的，所以在数据库中进行分页的时候，就需要对数据进行编号，这个时候就要使用SQl中的 ROW_NUMBER函数，

使用ROW_NUMBER函数查询的SQl语句和显示结果如下：

很容易的我们发现在id的前面有了一个按照id正序排列的rl的列。

所以，完整的 SQL分页语句为：n为我们传入的分页的页码，默认的每页显示的数据位5条

 select * from
 (
 select * from T_Person
 )as a
 where id>(n-1)*5 and id<=n*5

这样，我们就可以建立相应的分页存储过程，在数据库中对数据进行分页，然后供程序进行调用。

最后分享一个，在网上看到的一个比较好的分页存储过程：

create PROCEDURE GetPageData
 (
 @TableName varchar(30),--表名称
 @IDName varchar(20),--表主键名称
 @PageIndex int,--当前页数 
 @PageSize int--每页大小 
 )
 AS
 IF @PageIndex > 0
 BEGIN
 set nocount on
    DECLARE @PageLowerBound int,@StartID int,@sql nvarchar(225)
    SET @PageLowerBound = @PageSize * (@PageIndex-1)
    IF @PageLowerBound<1
     SET @PageLowerBound=1
    SET ROWCOUNT @PageLowerBound
    SET @sql=N'SELECT @StartID = ['+@IDName+'] FROM '+@TableName+' ORDER BY '+@IDName
      exec sp_executesql @sql,N'@StartID int output',@StartID output
    SET ROWCOUNT 0
    SET @sql='select top '+str(@PageSize) +' * from '+@TableName+' where ['+@IDName+']>='+ str(@StartID) +' ORDER BY ['+@IDName+'] '
    EXEC(@sql)
 set nocount off
 END