SQL2005分页查询

最新推荐文章于 2011-08-22 17:58:43 发布

xuexiaodong2009

最新推荐文章于 2011-08-22 17:58:43 发布

阅读量5.6k

点赞数 1

分类专栏：数据库文章标签： sql output 存储数据库

本文链接：https://blog.csdn.net/xuexiaodong009/article/details/6336638

版权

数据库专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1 通过select top进行分页查询

/*查询原理：需要查询第N页时，首先取到前N页的所有数据，然后排除前N-1页的数据，就是第N页的数据*/
create  PROCEDURE GetDataWithPage
( 
       @pageIndex  int = 1,           -- 页码
      @pageSize   int = 20,          -- 页尺寸  
)
as
    begin
     if @pageIndex < 1
begin
Set @pageIndex=1
end                 


--如果是第一页时要执行的语句
  if @PageIndex = 1
      begin
              select top ((@PageIndex)*@PageSize) 
               field1,field2,--查询字段
               fieldOrderby --排序字段，按什么字段分页的字段，建议使用主键，或者唯一键
               from tableName --查询表名称
              where id>100 --查询条件
              order by fieldOrderby --排序字段，按什么字段分页的字段，
             select count(*) as Total from tableName where id>100--返回总记录数
    end
  else  
         begin
                 select top ((@PageIndex)*@PageSize) --取出前PageIndex页的数据
                            field1,field2,--查询字段
                            fieldOrderby --排序字段，按什么字段分页的字段，建议使用主键，或者唯一键
                            into #tempTable --插入临时表
                            from tableName --查询表名称
                            where id>100 --查询条件
                           order by fieldOrderby --排序字段，按什么字段分页的字段，
                  -----取出前pageIndex页数据插入临时表

                 ------排除前pageIndex-1页的数据，取出第pageIndex的数据
                  select top (@PageIndex) --
                             field1,field2,--排序字段
                            fieldOrderby --
                           from #tempTable --从临时表中取数据
                          where fieldOrderby --
                          not in (select  top ((@PageIndex-1)*@PageSize) fieldOrderby from #tempTable)
                        ---- 从临时表取出 pageIndex的数据 
                            ----可以根据升序或者降序把not in 改为 <min(fieldOrderby)或者>max(fieldOrderby)
                 select count(*) as Total from tableName where id>100--返回总记录数
       end
end

2通过系统存储过程进行分页查询

 /*
 一共返回三个表第一个表为空 查询字符串的条件中有 like ,in 可能出现问题，具体出现原因不明，第二个表包含总页数，总行数，
当前页第三个表包含查询记录

*/
create   procedure [dbo].[GetOnePageData]
   @sqlstr nvarchar(4000), --查询字符串  ，就是一般的查询语句，不需要top关键字
   @currentpage int, --第N页
   @pagesize int --每页行数
as
   set nocount on
   declare @P1 int, --P1是游标的id
   @rowcount int--
    exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
   --定义与游标和游标选项相关联的 SQL 语句，然后填充游标。
   --选择总页数，总行数，当前页
    select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage
    set @currentpage=(@currentpage-1)*@pagesize+1
   --查询记录
    exec sp_cursorfetch @P1,16,@currentpage,@pagesize --从游标中提取一行或一个行块。
   --返回值
    exec sp_cursorclose @P1--关闭并释放游标
     set nocount off
go

3 通过新函数ROW_NUMBER()进行分页查询

/*
适用于sql2005，据调查此方法限制最少，性能最佳
返回两个表，第一个表包含总行数，第二个表是查询到的记录
--分页查询的原理：
--1.先将预分页内容按照排序条件加上自增列导入到临时表中（或表变量）
--2.针对临时表操作，找到相应的N页对应的自增列编码范围
--3.根据第N页对应的自增列编码范围，查找第N页内容
 
--需要注意的是：
--第一是添加自增列，确定行号
--第二缩减中间处理过程的操作数据量

*/

create  proc [dbo].[GetOnePageData]--//
(
    @page int,
    @pagesize int
)
as
begin    
        select top  (@pagesize*@page)  ROW_NUMBER() --可以减少处理的数据，加快速度
         OVER(ORDER BY NodeID) as rowNum,--按什么字段分页的字段，不需要唯一性
        NodeID,NodeName,ControlIP --查询字段
        into #temp --插入临时表
        from TableName   --  查询表名称 
        where NodeID>100 --查询条件
       
       select count(*) from #temp--总记录条数
       select * from #temp where rowNum>@pagesize*(@page-1) and rowNum<=@pagesize*@page--
end

综述，如果需要通用的存储过程，可以把第一或者第三种分页方法进行改写，在数据库拼字符串，但性能会差好多。这或许就是通用和性能之间的矛盾吧。