sql server 分批查询

数据库中有几万条记录，我想分批查询，每次1000条，该如何实现

解决方案 »

  1.   

    select * from table1 limit 0,1000;
      

  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
      

  3.   

    --参考（存储过程）
          
      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