Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/2/3 6:46:05 数据库的分页问题_[SQL Server教程]
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)asbegin 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)asbegin 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) endendgo
另外, 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 productidend
不尽如此,这种新功能有许多妙用, 如可以取班级排名前N名或第n名到第m名的学生等等,这类问题将会在我以后的文章中进行讨论!这是我的第一篇博客, 呵呵,处来砸到,讨论的问题希望对大家有所帮助,另外,如有疑难或错误之处,敬请指出!http://www.cnblogs.com/alanding/archive/2007/02/02/638197.html
赞