我的漫漫程序之旅

专注于JavaWeb开发
随笔 - 39, 文章 - 310, 评论 - 411, 引用 - 0
数据加载中……

SQL之通用查询分页存储过程

--创建测试表
if object_id('person'is not null
  
drop table person

create table person 
(
  id 
int primary key identity(1,1),
  username 
varchar(50),
  age 
int,
  remark 
varchar(100)
)

--插入测试数据
declare @i int
set @i = 0
while @i < 100
 
begin
   
insert into person values('zdw' + convert(varchar(5),@i),@i,convert(varchar(5),@i))
   
set @i = @i + 1
  
end
go

select * from person

 
/*   
  * 分页存储过程
  * 功能描述:对指定表中满足条件的记录按指定列进行分页查询,分页可以顺序、倒序   
  * 查询可以指定页大小、指定查询任意页、指定输出字段列表    
  
*/
   
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   
 
  
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   
    
--测试
exec sp_page  'person','id',0,0,'*',5,10,8,'','','id != 77'
对于比较简单的分页需求,一句sql搞定。
--第一个10表示pagesize,第二个10用pagesize*(pagenum-1)来算
--
比如取第2页10 * (2-1) ,第3页 10 * (3 -1)
select top 10 * from person where id not in (select top 10 id from person)
大家还有什么好的分页sql都拿出来分享下吧。



posted on 2008-11-05 09:44 々上善若水々 阅读(1689) 评论(0)  编辑  收藏 所属分类: 数据库


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


网站导航: