每隔N行生成一個彙總行(總結)

壹頁書發表於2017-12-26
接前文
http://blog.itpub.net/29254281/viewspace-2149120/
http://blog.itpub.net/29254281/viewspace-2149309/

處理這種每隔N行生成一個彙總行的需求，一共有三種方式


  1. drop table t;
  2. create table t (c int);
  3. insert into t values
  4. (15),(7),(9),(10),(7),(8),(20),(16),(9),(19),
  5. (14),(10),(11),(10),(10),(12),(7),(10),(7),(9);
  6. commit;


  9. select * from t;


為了簡單，就按照這個自然順序 ，每三行生成一個彙總行,sum前三行的值。

第一招，效率低下容易理解
  1. select case when c is null then '彙總' else '' end s,ifnull(c,sumc) c from (  
  2.     select ceil(id/3) l,c,null sumc  
  3.     from   
  4.     (  
  5.         select @id:=@id+1 id, t1.* from t t1,(select @id:=0) vars    
  6.     ) t2  
  7.     union all    
  8.     select ceil(id/3) l,null,sum(c)   
  9.     from   
  10.     (  
  11.         select @id1:=@id1+1 id, t1.* from t t1,(select @id1:=0) vars    
  12.     ) t3  
  13.     group by l  
  14. ) t order by l,ifnull(c,'9999'); 

結果：


第二招，數字輔助表補全. 效能較好. 計算彙總行,理解稍微複雜.

先增加一個數字輔助表 nums
  1. create table nums(id int not null primary key);

  3. delimiter $$
  4. create procedure pCreateNums(cnt int)
  5. begin
  6.     declare s int default 1;
  7.     truncate table nums;
  8.     while s<=cnt do
  9.         insert into nums select s;
  10.         set s=s+1;
  11.     end while;
  12. end $$
  13. delimiter ;

  15. delimiter $$
  16. create procedure pFastCreateNums(cnt int)
  17. begin
  18.     declare s int default 1;
  19.     truncate table nums;
  20.     insert into nums select s;
  21.     while s*2<=cnt do
  22.         insert into nums select id+s from nums;
  23.         set s=s*2;
  24.     end while;
  25. end $$
  26. delimiter ;

初始化數字輔助表
call pFastCreateNums(100000);

然後

  1. select s,ifnull(c,cc) c  
  2. from (    
  3.     select     
  4.     case when rn is null then '彙總' else '' end s,     
  5.     t4.c,  
  6.     if(mod(t3.id,4)!=0 ,case when @total=-1 then @total:=t4.c else @total:=@total+t4.c end,@total) cc,  
  7.     case when mod(t3.id,4)=0 then @total:=-1 else null end     
  8.     from (    
  9.         select * from nums where id<=    
  10.         (select (ceil(count(*)/4)+1)*5  from t )    
  11.     ) t3    
  12.     left join (    
  13.         select     
  14.         case when mod(@rn+1,4)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn ,    
  15.         t1.* from t t1,(select @rn:=0,@total:=0) vars      
  16.     ) t4 on(t3.id=t4.rn)    
  17. ) result;    

這個理解稍微有點複雜,
第二招改進版本
  1. select s,ifnull(c,cc) c    
  2. from (      
  3.     select       
  4.     case when rn is null then '彙總' else '' end s,       
  5.     t4.c,    
  6.     case when t4.c is not null then @total:=@total+t4.c when t4.c is null then @total+(@total:=0) end cc       
  7.     from (      
  8.         select * from nums where id<=      
  9.         (select (ceil(count(*)/4)+1)*5  from t )      
  10.     ) t3      
  11.     left join (      
  12.         select       
  13.         case when mod(@rn+1,4)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn ,      
  14.         t1.* from t t1,(select @rn:=0,@total:=0) vars        
  15.     ) t4 on(t3.id=t4.rn)      
  16. ) result where ifnull(c,cc) is not null;  

好理解多了,只是有冗餘行數。



第三招，又是王工的大招. 效能比第二招快15%-20%。可讀性強 好理解。整體還簡短。
  1. select case when id is null then '彙總' else '' end s,sum(c) c from (  
  2.     select @id:=@id+1 id, t1.* from t t1,(select @id:=0) vars      
  3. ) t2  
  4. group by ceil(t2.id/3),t2.id with rollup;  


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2149337/，如需轉載，請註明出處，否則將追究法律責任。

相關文章