MSSQLServer数据库的分页问题

原创 2007年09月21日 08:26:00

        随着数据库中存储的数据的增多,满足用户查询条件的数据也随之增加。而用户一般不可能一次性看完所有的数据, 很多时候也不需要看完所有数据。在这种情况下,分页返回用户查询的数据就显得相当的重要。分页返回用户数据有如下好处:
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
)
as
begin
    
if object_id(@tableNameis 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
)
as
begin
    
if object_id(@tableNameis 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(@sql11, 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() 实现分页处理:

create proc up_GetPagen
(
    
@pageSize int
    
@pageCurrent int
)
as 
begin
    
select * from 
    (
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名的学生等等,这类问题将会在我以后的文章中进行讨论!这是我的第一篇博客, 呵呵,处来砸到,讨论的问题希望对大家有所帮助,另外,如有疑难或错误之处,敬请指出!

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

数据库(MSSQLServer,Oracle,DB2,MySql)常见语句以及问题(续1之拼接字符串)

上一篇文章http://www.cnblogs.com/valiant1882331/p/4056403.html写的太长了,所以就换了一篇,链接上一节继续 字符串的拼接 MySq...

数据库中的分页问题

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

MSSQLSERVER数据库- 变量和批处理

当作学习总结吧!以下的内容很早以前就有人写过。但我还是要再写一下,一是练一下自己写这方面文章的能力,二是对知识点的学习有一下重温和加深。         SQL语言也跟其他编程语言一样,拥有变量、分...
  • cxeye
  • cxeye
  • 2012-10-16 12:44
  • 230

MSSQLSERVER数据库- 使用C#来操作事务

就在不久前,我在博客园看到一个名为英雄默问出处的博客。他里面有两篇是介绍用C#来操作事务的。用C#来操作事务和用SQL语句来操作事务原理是一模一样的。总结起来有三个步骤:   1.开启事务   2...
  • cxeye
  • cxeye
  • 2012-10-17 19:30
  • 372

mssqlserver无法远程数据库Error

我们在在使用SQL Server时都会遇到使用SQL Server Management Studio无法连接远程数据库实例的问题,错误描述信息摘录如下: An error has occurred...

MSSQLSERVER数据库- 存储过程

写这篇存储过程的前参看了五六篇别人写的文章,看完后学到一些以前没有发现的东西,原来存储过程里有系统存储过程,原来存储过程还可以有返回值,我将把我从别人那里看到的,重新总结一下写出来。       什...
  • cxeye
  • cxeye
  • 2012-10-16 12:52
  • 291

MSSQLSERVER数据库-多表查询inner join

好些时间都没有呆在宿舍搞C#和SQL了. 昨天因为要完成老师的作业, 又开始重操旧业. 转正题, 说说昨晚遇到的一个小问题, 关于使inner join来进行多表查询. 对于没有从事过真正项目开发...
  • cxeye
  • cxeye
  • 2012-10-14 19:16
  • 365

MSSQLSERVER数据库- 事务

我接触.NET编程已经有两年了,在这之前就会一点ASP和SQL语句。刚接触时在网上请教一个从事ASP.NET工作不久的网友,什么是事务?  当时他没答上。事实上我接触.NET这两年里,做一些小例子,也...
  • cxeye
  • cxeye
  • 2012-10-17 19:25
  • 243

MSSQLSERVER数据库- 事务

我接触.NET编程已经有两年了,在这之前就会一点ASP和SQL语句。刚接触时在网上请教一个从事ASP.NET工作不久的网友,什么是事务?  当时他没答上。事实上我接触.NET这两年里,做一些小例子,也...

MSSQLSERVER数据库- 存储过程

写这篇存储过程的前参看了五六篇别人写的文章,看完后学到一些以前没有发现的东西,原来存储过程里有系统存储过程,原来存储过程还可以有返回值,我将把我从别人那里看到的,重新总结一下写出来。      ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)