分页存储过程
SQL2005存储过程分页查询
2009-02-14 20:06
select * from (select goods_id,ROW_NUMBER() OVER (order by goods_name) as curr from goods ) as t where t.curr>=startrow and t.curr<= endrow 其中satrtrow endrow 分别表示当前页的起始行 终止行 具体可用 startrow = (intpage-1)*perpagesize +1 endrow = intpage*perpagesize SQL Server 2005 的分页查询(ROW_NUMBER() OVER 语句) DECLARE @rows_per_page AS INTEGER DECLARE @current_page AS INTEGER DECLARE @total_pages AS INTEGER DECLARE @start_item AS INTEGER DECLARE @items_count AS INTEGER -- 设置每页的行数 SET @rows_per_page = 10 -- 设置要显示的页号(从1开始) SET @current_page = 3 SELECT @total_pages = COUNT(*) / @rows_per_page + 1, @items_count= COUNT(*) FROM BarefootIndex;--表名(这是修改的地方) --计算此页中从第几个开始显示 SET @start_item = @rows_per_page * (@current_page - 1) select * from ( select ROW_NUMBER() OVER (order by ID) as item--用什么排序(返回正在显示第几条) ,@items_count AS items_count --一共有多少条 ,@current_page AS current_page --当前页 ,@total_pages AS total_pages --一共多少页 ,* from BarefootIndex--表名(这是修改的地方) ) as T where T.item >= @start_item + 1 AND T.item <= @start_item + @rows_per_page ----------------------------------------------------------------------- Sql Server 2005出来已经很久了,一些新的功能应该得到更好的应用,下面是利用2005里面新的函数 ROW_NUMBER() 写的分页存储过程(一个老外写的,我就直接Copy过来了): @datasrc - the table (or stored procedure, etc.) name @orderBy - the ORDER BY clause @fieldlis - the fields to return (including calculated expressions) @filter - the WHERE clause @pageNum - the page to return (must be greater than or equal to one) @pageSize - the number of records per page CREATE PROCEDURE [dbo].[utilPAGE] @datasrc nvarchar(200) ,@orderBy nvarchar(200) ,@fieldlist nvarchar(200) = '*' ,@filter nvarchar(200) = '' ,@pageNum int = 1 ,@pageSize int = NULL AS SET NOCOUNT ON DECLARE @STMT nvarchar(max) -- SQL to execute ,@recct int -- total # of records (for GridView paging interface) IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1' IF @pageSize IS NULL BEGIN SET @STMT = 'SELECT ' + @fieldlist + 'FROM ' + @datasrc + 'WHERE ' + @filter + 'ORDER BY ' + @orderBy EXEC (@STMT) -- return requested records END ELSE BEGIN SET @STMT = 'SELECT @recct = COUNT(*) FROM ' + @datasrc + ' WHERE ' + @filter EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT SELECT @recct AS recct -- return the total # of records DECLARE @lbound int, @ubound int SET @pageNum = ABS(@pageNum) SET @pageSize = ABS(@pageSize) IF @pageNum < 1 SET @pageNum = 1 IF @pageSize < 1 SET @pageSize = 1 SET @lbound = ((@pageNum - 1) * @pageSize) SET @ubound = @lbound + @pageSize + 1 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the -- specified page END SET @STMT = 'SELECT ' + @fieldlist + ' FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, * FROM ' + @datasrc + ' WHERE ' + @filter + ' ) AS tbl WHERE row > ' + CONVERT(varchar(9), @lbound) + ' AND row < ' + CONVERT(varchar(9), @ubound) EXEC (@STMT) -- return requested records END ------------------------------------------------------------- 我们以前在开发大数据量的分页存储过程时,往往都为了怎么样实现高效的性能,而大伤脑筋,似乎总 是想写出最佳的存储过程分页方法,我们假如建立一个学生基本信息表StudentInfo,我们看在Sql Server 2000中我们实现的存储过程 CREATE PROCEDURE p_GetStudentInfo @strWhere varchar(1500) -- 查询条件 (注意: 不要加 where) ,@PageSize int = 50 -- 页尺寸 ,@PageIndex int = 1 -- 页码 AS BEGIN declare @strSQL varchar(5000) -- 主语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 set @strTmp = '<(select min' set @strOrder = ' order by studentinfoid desc' if @PageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo '+ @strOrder --如果是第一页就执行以上代码,这样会加快执行速度 end else begin --以下代码赋予了@strSQL以真正执行的SQL代码 set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo where studentinfoid' + @strTmp + '(studentinfoid) from (select top ' + str((@PageIndex-1) *@PageSize) + ' studentinfoid from studentinfo' + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo where studentinfoid' + @strTmp + '(studentinfoid) from (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder end --print (@strSQL) exec (@strSQL) END GO 我们在上面的存储过程中可以看到,如果执行的是第一页的记录时,我们可以看到只执行 'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' + @strOrder 就可以了,而如果翻页的时候,就会在条件查询中又嵌套子查询,势必性能会有所下降,而这个已经 在我们Sql Server2000中算是高效的分页存储过程实现方式了,而现在如果我们换作Sql Server 2005的 时候,我们是不是还是用这种方式呢? 在Sql Server 2005中,我们可以利用新增函数row_number()来更高效的实现分页存储 CREATE PROCEDURE p_GetStudentInfo @PageSize INT, @PageIndex INT, @strWhere varchar(1500) -- 查询条件(注意: 不要加where) As Begin select * from ( select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex End go 依照群组显示每条记录在该群组中出现的顺序位置,在显示每条记录编号时非常有用,并且搭配OVER子句 ,这样就可以实现查询记录的条数了。 我们现在来比较一下它们执行的性能,StudentInfo表中现在有30多万条数据,我们分别来执行下面 的存储过程: Exec p_GetStudentInfo @PageSize = 10000, @PageIndex = 10, @strWhere = '1=1' 在Sql Server 2000中,执行的时间是接近2秒; 在Sql Server 2005中,执行的时间显示的是接近0秒。 如果数据涉及到千万级的数据时,比较上面两种方法,就可以看到一些显著的差别了。简单的样子。 --------------------------------------------------------- SQL2005增加了不少新特性,其中NTILE和ROW_NUMER使得我们不再为SQL如何灵活方便的分页伤脑筋了(不 必再羡慕Oracle等数据库了)。 下面就是一个很简单的分页查询语句: DECLARE @rows_per_page AS INTEGER DECLARE @current_page AS INTEGER DECLARE @total_pages AS INTEGER -- 设置每页的行数 SET @rows_per_page = 20 -- 设置要显示的页号(从1开始) SET @current_page = 2 -- 计算总页数 SELECT @total_pages = COUNT(*) / @rows_per_page FROM testtable; -- 列出指定页的内容 WITH t AS ( SELECT NTILE(@total_pages) OVER(ORDER BY id) AS page_number, * FROM testtable ) SELECT * from t WHERE page_number = @current_page 程序简单到可以不用说明的程度。 我们可以利用上述简单的语句,变化排序条件和查询表,就可以做出一个很通用的分页查询的存储过 程或查询语句了。 同样的,使用ROW_NUMBER也可以做到分页查询: DECLARE @rows_per_page AS INTEGER DECLARE @current_page AS INTEGER DECLARE @start_row_num AS INTEGER -- 设置每页的行数 SET @rows_per_page = 20 -- 设置要显示的页号(从1开始) SET @current_page = 2 -- 设置开始行号 SET @start_row_num = (@current_page - 1) * @rows_per_page WITH t AS ( SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, * FROM testtable ) SELECT * from t WHERE row_number BETWEEN @start_row_num AND @start_row_num + @rows_per_page 似乎更简单的样子。 |