传说中的分页2

 CREATE   PROCEDURE   sp_page  
      @tb                   varchar(50),   --表名  
      @col                 varchar(50),   --按该列来进行分页  
      @coltype         int,                   --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型  
      @orderby         bit,                   --排序,0-顺序,1-倒序  
      @collist         varchar(800),--要查询出的字段列表,*表示全部字段  
      @selecttype   int,                   --查询类型,1-前页,2-后页,3-首页,4-末页,5-指定页  
      @pagesize       int,                   --每页记录数  
      @page               int,                   --指定页  
      @minid             varchar(50),   --当前页最小号  
      @maxid             varchar(50),   --当前页最大号  
      @condition     varchar(800)   --查询条件  
  AS  
  /*  
  功能描述:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序  
                    查询可以指定页大小、指定查询任意页、指定输出字段列表  
  作         者:pbsql  
  版         本:1.01  
  最后修改:2004-11-26  
  */  
  DECLARE   @sql   nvarchar(4000),@where1   varchar(800),@where2   varchar(800)  
  DECLARE   @i   int,@id   varchar(50)  
  IF   @coltype=1   or   @coltype=2--字段类型为字符或日期时间要加上引号以作比较用  
  BEGIN  
      SET   @minid=''''+@minid+''''  
      SET   @maxid=''''+@maxid+''''  
  END  
  IF   @condition   is   null   or   rtrim(@condition)=''--没有查询条件  
  BEGIN  
      SET   @where1='   WHERE   '  
      SET   @where2='     '  
  END  
  ELSE--有查询条件  
  BEGIN  
      SET   @where1='   WHERE   ('+@condition+')   AND   '--本来有条件再加上此条件  
      SET   @where2='   WHERE   ('+@condition+')   '--原本没有条件而加上此条件  
  END  
  SET   @sql=  
      CASE   @selecttype  
          WHEN   1--前页  
          THEN   'SELECT   *   FROM   (SELECT   TOP   '+CAST(@pagesize   AS   varchar)+  
                    '   '+@collist+'   FROM   '+@tb+@where1+@col+  
                    CASE   @orderby   WHEN   0   THEN   '<'+@minid   ELSE   '>'+@maxid   END+  
                    '   ORDER   BY   '+@col+CASE   @orderby   WHEN   0   THEN   '   DESC'   ELSE   ''   END+  
                    ')   t   ORDER   BY   '+@col+CASE   @orderby   WHEN   0   THEN   ''   ELSE   'DESC'   END  
          WHEN   2--后页  
          THEN   'SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                    '   FROM   '+@tb+@where1+@col+  
                    CASE   @orderby   WHEN   0   THEN   '>'+@maxid   ELSE   '<'+@minid   END+  
                    '   ORDER   BY   '+@col+CASE   @orderby   WHEN   0   THEN   ''   ELSE   '   DESC'   END  
          WHEN   3--首页  
          THEN   'SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                    '   FROM   '+@tb+@where2+'ORDER   BY   '+@col+  
                    CASE   @orderby   WHEN   0   THEN   ''   ELSE   '   DESC'   END  
          WHEN   4--末页  
          THEN   'SELECT   *   FROM   (SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+  
                    @collist+'   FROM   '+@tb+@where2+'ORDER   BY   '+@col+  
                    CASE   @orderby   WHEN   0   THEN   '   DESC'   ELSE   ''   END+')   t   ORDER   BY   '+  
                    @col+CASE   @orderby   WHEN   0   THEN   ''   ELSE   '   DESC'   END  
      END  
  IF   @selecttype>=1   and   @selecttype<=4  
  BEGIN  
      EXEC(@sql)  
      RETURN  
  END  
  ELSE  
  BEGIN--指定页  
      IF   @coltype=1  
          IF   @orderby   =0  
              SET   @id=''''''  
          ELSE  
              SET   @id=''''+CHAR(255)+''''  
      ELSE  
          IF   @coltype=2  
              IF   @orderby   =0  
                  SET   @id='''1753-1-1'''  
              ELSE  
                  SET   @id='''9999-12-31'''  
          ELSE  
              IF   @orderby   =0  
                  SET   @id='-2147483648'  
              ELSE  
                  SET   @id='2147483647'  
      SET   @i=0  
      --为减少之后SELECT   TOP   ...的数据量,此处每10000条循环一次,以尽可能接近所查询页  
      WHILE   @i<@pagesize*@page  
      BEGIN  
          IF   @i+10000<@pagesize*@page  
          BEGIN  
              IF   @orderby=0  
                  SET   @sql='SELECT   @id=CASE   '+CAST(@coltype   AS   varchar)+  
                      '   WHEN   1   THEN   ''''''''+CAST(MAX('+@col+')   AS   varchar(50))+'+  
                      ''''''''''+  
                      '   WHEN   2   THEN   ''''''''+CONVERT(char(23),MAX('+@col+'),121)+'+  
                      ''''''''''+  
                      '   ELSE   CAST(MAX('+@col+')   AS   varchar)   END   FROM   (SELECT   TOP   10000   '+  
                      @col+'   FROM   '+@tb+@where1+@col+'>'+@id+'   ORDER   BY   '+@col+')   t'  
              ELSE  
                  SET   @sql='SELECT   @id=CASE   '+CAST(@coltype   AS   varchar)+  
                      '   WHEN   1   THEN   ''''''''+CAST(MIN('+@col+')   AS   varchar(50))+'+  
                      ''''''''''+  
                      '   WHEN   2   THEN   ''''''''+CONVERT(char(23),MIN('+@col+'),121)+'+  
                      ''''''''''+  
                      '   ELSE   CAST(MIN('+@col+')   AS   varchar)   END   FROM   (SELECT   TOP   10000   '+  
                      @col+'   FROM   '+@tb+@where1+@col+'<'+@id+'   ORDER   BY   '+@col+'   DESC)   t'  
              EXEC   sp_executesql   @sql,N'@id   varchar(50)   OUTPUT',@id   OUTPUT  
              SET   @i=@i+10000  
              IF   @i+10000>=@pagesize*@page  
                  BREAK  
          END  
          ELSE  
              BREAK  
      END  
      --上面的循环保证下面的子查询最多只有10000条数据  
      IF   @orderby=0  
          SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                            '   FROM   '+@tb+@where1+@col+'>'+@id+'   AND   '+@col+'   NOT   IN'+  
                            '(SELECT   TOP   '+CAST(@pagesize*(@page-1)-@i   AS   varchar)+  
                            '   '+@col+'   FROM   '+@tb+@where1+@col+'>'+@id+'   ORDER   BY   '+@col+  
                            ')   ORDER   BY   '+@col  
      ELSE  
          SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                            '   FROM   '+@tb+@where1+@col+'<'+@id+'   AND   '+@col+'   NOT   IN'+  
                            '(SELECT   TOP   '+CAST(@pagesize*(@page-1)-@i   AS   varchar)+  
                            '   '+@col+'   FROM   '+@tb+@where1+@col+'<'+@id+'   ORDER   BY   '+@col+  
                            '   DESC)   ORDER   BY   '+@col+'   DESC'  
      EXEC(@sql)  
  END  
  GO  
   
   
  --测试事例  
  select   identity(int,1,1)   id,getdate()   dt,xx=cast(''   as   varchar(10))   into   #t  
    from   sysobjects  
  update   #t   set   dt=dateadd(day,id-200,dt),  
                              xx='xxxx'+right('000000'+cast(id   as   varchar(10)),6)  
  --exec   sp_page   '#t','id',0,0,'*',5,10,3,'','',''--按id顺序取第三页  
  --exec   sp_page   '#t','id',0,1,'*',5,10,3,'','',''--按id倒序取第三页  
  --exec   sp_page   '#t','xx',1,0,'*',1,10,3,'xxxx000021','xxxx000030',''--按xx顺序取前一页  
  --exec   sp_page   '#t','xx',1,1,'*',2,10,3,'xxxx000134','xxxx000143',''--按xx倒序取后一页  
  --exec   sp_page   '#t','dt',2,0,'*',4,10,3,'','',''--按dt顺序取最后一页  
  exec   sp_page   '#t','dt',2,1,'*',3,10,3,'','',''--按dt倒序取首页  
  drop   table   #t  
   
   
   
   
  改善了一下性能,加了个返回总页数  
   
  CREATE   PROCEDURE   sp_page  
      @tb                   varchar(50),   --表名  
      @col                 varchar(50),   --按该列来进行分页  
      @coltype         int,                   --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型  
      @orderby         bit,                   --排序,0-顺序,1-倒序  
      @collist         varchar(800),--要查询出的字段列表,*表示全部字段  
      @pagesize       int,                   --每页记录数  
      @page               int,                   --指定页  
      @condition     varchar(800),--查询条件  
      @pages             int   OUTPUT       --总页数  
  AS  
  /*  
  功能描述:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序  
                    查询可以指定页大小、指定查询任意页、指定输出字段列表,返回总页数  
  作         者:pbsql  
  版         本:1.10  
  最后修改:2004-11-29  
  */  
  DECLARE   @sql   nvarchar(4000),@where1   varchar(800),@where2   varchar(800)  
  IF   @condition   is   null   or   rtrim(@condition)=''  
  BEGIN--没有查询条件  
      SET   @where1='   WHERE   '  
      SET   @where2='     '  
  END  
  ELSE  
  BEGIN--有查询条件  
      SET   @where1='   WHERE   ('+@condition+')   AND   '--本来有条件再加上此条件  
      SET   @where2='   WHERE   ('+@condition+')   '--原本没有条件而加上此条件  
  END  
  SET   @sql='SELECT   @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize   AS   varchar)+  
                    ')   FROM   '+@tb+@where2  
  EXEC   sp_executesql   @sql,N'@pages   int   OUTPUT',@pages   OUTPUT--计算总页数  
  IF   @orderby=0  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                        '   FROM   '+@tb+@where1+@col+'>(SELECT   MAX('+@col+')   '+  
                        '   FROM   (SELECT   TOP   '+CAST(@pagesize*(@page-1)   AS   varchar)+'   '+  
                        @col+'   FROM   '+@tb+@where2+'ORDER   BY   '+@col+')   t)   ORDER   BY   '+@col  
  ELSE  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+  
                        '   FROM   '+@tb+@where1+@col+'<(SELECT   MIN('+@col+')   '+  
                        '   FROM   (SELECT   TOP   '+CAST(@pagesize*(@page-1)   AS   varchar)+'   '+  
                        @col+'   FROM   '+@tb+@where2+'ORDER   BY   '+@col+'   DESC)   t)   ORDER   BY   '+  
                        @col+'   DESC'  
  IF   @page=1--第一页  
      SET   @sql='SELECT   TOP   '+CAST(@pagesize   AS   varchar)+'   '+@collist+'   FROM   '+@tb+  
          @where2+'ORDER   BY   '+@col+CASE   @orderby   WHEN   0   THEN   ''   ELSE   '   DESC'   END  
  EXEC(@sql)  
  GO  
   
  详情见:  
  http://blog.youkuaiyun.com/pbsql/archive/2004/11/30/199657.aspx 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值