分页存储过程

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　　 似乎更简单的样子。

转载于:https://www.cnblogs.com/zhangqingtao/archive/2009/08/26/1554308.html