数据库的分页问题 (转)-优快云博客

随着数据库中存储的数据的增多，满足用户查询条件的数据也随之增加。而用户一般不可能一次性看完所有的数据，很多时候也不需要看完所有数据。在这种情况下，分页返回用户查询的数据就显得相当的重要。分页返回用户数据有如下好处：
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语句。
以下是使用上述原理实现的通用分页处理存储过程：

create proc up_PageView

(

@tableName sysname,

@colKey nvarchar ( 100 ),

@pageCurrent int = 1 ,

@pageSize int = 10 ,

@colShow nvarchar ( 4000 ) = '' ,

@colOrder nvarchar ( 200 ) = '' ,

@where nvarchar ( 2000 ) = '' ,

@pageCount int output

)

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子句配合纪录的指定页主健就可以查询出最终的结果来。下面是改进的存储过程：

create proc up_PageView

(

@tableName sysname,

@colKey nvarchar ( 100 ),

@pageCurrent int = 1 ,

@pageSize int = 10 ,

@colShow nvarchar ( 4000 ) = '' ,

@colOrder nvarchar ( 200 ) = '' ,

@where nvarchar ( 2000 ) = '' ,

@pageCount int output

)

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

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

create proc up_GetPagen

(

@pageSize int ,

@pageCurrent int

)

begin

select * from