数据库的分页问题

随着数据库中存储的数据的增多，满足用户查询条件的数据也随之增加。而用户一般不可能一次性看完所有的数据， 很多时候也不需要看完所有数据。在这种情况下，分页返回用户查询的数据就显得相当的重要。分页返回用户数据有如下好处：

1、减少服务器磁盘系统地读取压力
2、减少网络流量，减轻网络压力
3、减轻客户端显示数据的压力
4、提高处理效率。

一般而言，分页处理分为两种：应用程序中的分页处理和数据库中的分页处理。目前大多数的应用都是在应用程序中借助支持数据分页处理的数据库访问组 件（如DataGrid控件）实现分页处理。实际上，在数据库中实现分页处理，可以从源头减少数据处理量，效果往往可能跟明显。本文主要讨论数据库的分页 问题。

常规的取第n页数据方法为： 

Select top PageSize * 
from TableA 
where Primary_Key not in (select top (n-1)*PageSize Primary_Key from TableA )

对于应用程序而言，所做的就是在生成分页处理的T-SQL语句前先计算好各数字， 对于数据库而言，应该采用动态的T-SQL语句。

以下是使用上述原理实现的通用分页处理存储过程：

 1create proc up_PageView
 2(
 3    @tableName sysname,
 4    @colKey    nvarchar(100),
 5    @pageCurrent int = 1,
 6    @pageSize int = 10,
 7    @colShow nvarchar(4000) = '',
 8    @colOrder nvarchar(200) = '',
 9    @where nvarchar(2000) = '',
    @pageCount int output
)
as
begin
    if object_id(@tableName) is null
    begin
    raiserror('the table is not existing!', 16,1)
    return 
    end
    if  isnull(@colShow, '') = ''
        set @colShow  = '*'
    if isnull(@colOrder,'') = ''
        set @colOrder = ''
    else 
        set @colOrder = 'order by ' + @colOrder
    if isnull(@where, '') = ''
        set @where = ''
    else
        set @where = 'where '+ @where
    declare @sql nvarchar(4000)
    if @pageCount is null
        begin
        set @sql = 'select @pageCount = count(*) from ' + @tableName + ' ' + @where
        Exec sp_executesql @sql, '@pageCount int output', @pageCount output
        set @pageCount = (@pageCount + @pageSize -1)/@pageSize
        end
    if @pageCurrent = 1
        set @sql = 'select top' +' ' + convert(nvarchar(10), @pageSize) + ' '
                + @colshow + ' ' + 'from ' + @tableName + ' ' + @where + ' ' + @colOrder 
    else
        begin
        set @sql = 'select top' +' ' + convert(nvarchar(10), @pageSize) + ' '
                + @colshow + ' ' + 'from ' + @tableName + ' ' + @where 
        set @sql = @sql + ' ' + 'and '+ @colKey +' not in ( ' 
                + 'select top' +' ' + convert(nvarchar(10), (@pageCurrent - 1) * @pageSize) + ' '
                + @colKey + ' ' + 'from ' + @tableName + ' ' + @where  + ' )'
        set @sql = @sql + ' ' + @colOrder
        end
    --execute the dynamic query

    exec (@sql)
end

这种方法的缺点是为了排除该页以前的页， 必须使用top n取大量的数据并缓存起来，在关联元表查询出最终结果，这样做的效率比较低。通常情况 下， 我们都是对单主健（使用单个字段定位纪录）的表进行分页查询。因此，如果能使用一个字符串变量纪录指定页的所有主健，在使用in子句配合纪录的指定 页主健就可以查询出最终的结果来。下面是改进的存储过程：

 1create proc up_PageView
 2(
 3    @tableName sysname,
 4    @colKey    nvarchar(100),
 5    @pageCurrent int = 1,
 6    @pageSize int = 10,
 7    @colShow nvarchar(4000) = '',
 8    @colOrder nvarchar(200) = '',
 9    @where nvarchar(2000) = '',
    @pageCount int output
)
as
begin
    if object_id(@tableName) is null
    begin
    raiserror('the table is not existing!', 16,1)
    return 
    end
    if  isnull(@colShow, '') = ''
        set @colShow  = '*'
    if isnull(@colOrder,'') = ''
        set @colOrder = ''
    else 
        set @colOrder = 'order by ' + @colOrder
    if isnull(@where, '') = ''
        set @where = ''
    else
        set @where = 'where '+ @where
    declare @sql nvarchar(4000)
    if @pageCount is null
        begin
        set @sql = 'select @pageCount = count(*) from ' + @tableName + ' ' + @where
        Exec sp_executesql @sql, '@pageCount int output', @pageCount output
        set @pageCount = (@pageCount + @pageSize -1)/@pageSize -- get total pages

        end
    if @pageCurrent = 1
        begin
        set @sql = N'select top' +N' ' + convert(nvarchar(10), @pageSize) + N' '
                + @colshow + N' ' + N'from ' + @tableName + N' ' + @where + N' ' + @colOrder
        exec (@sql)
        end
    else
        begin
        declare @topN int, @topN1 int
--        set @topN = @pageSize

--        set @topN1 = @pageCurrent * @pageSize

        set @pageCurrent = @pageCurrent * @pageSize
        set @sql = N'select @n = @n - 1, @s = case when @n < ' + convert(nvarchar(10), @pageSize) +
                    N' then @s + '','' + quotename(@colKey, N'''''''') ' + N'else ''''' + N' end ' 
                    + N' from '+ @tableName + N' ' + @where
        --make query effect only @pageCurrent records

        --Query only top @pageCurrent * @pageSize

        set rowcount @pageCurrent 
        exec sp_executesql @sql, '@n int, @s nvarchar(max) output', @pageCurrent, @sql output
        set rowcount 0 --recover to default config

        set @sql = stuff(@sql, 1, 1, N'') -- remove the first ','

        --exec the query

        Exec (N'select ' + @colShow + N' ' + 'from' + N' ' + @tableName + N' '  
                + N'where ' + @colKey + N' in (' + @sql + ')' + @colOrder)
        end
end
go

另外， sql server 2005 增加了一些新的功能如取得排名或顺序的函数 （Rank(), Dense_Rank(), Row_Number()）, 利用这些新的功能也能进行分页处理，下面以 sql server 2005 自带的数据库AdventureWorks为例结合Row_Number() 实现分页处理：

 1create proc up_GetPagen
 2(
 3    @pageSize int, 
 4    @pageCurrent int
 5)
 6as 
 7begin
 8    select * from 
 9    (select ROW_NUMBER() over(ORDER BY productid) RowNum, * from production.product )OrderData
    where RowNum between (@pageCurrent - 1)*@pageSize + 1 and @pageCurrent * @pageSize
    order by productid
end

不尽如此，这种新功能有许多妙用， 如可以取班级排名前N名或第n名到第m名的学生等等，这类问题将会在我以后的文章中进行讨论！