yxhxj2006

常用链接

统计

最新评论

通过存储过程对SQLSERVER2005分页

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通过系统存储过程进行分页查询

[sql] view plaincopy
 /*  
 一共返回三个表第一个表为空 查询字符串的条件中有 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()进行分页查询

[sql] view plaincopy
/*  
适用于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   

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















posted on 2012-08-29 21:31 奋斗成就男人 阅读(800) 评论(0)  编辑  收藏 所属分类: SQL


只有注册用户登录后才能发表评论。


网站导航: