GROUP BY及GROUP BY的高阶用法

一、概述

使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来，当GROUPING SETS里面的分组元素越多时，使用GROUPING SETS比使用UNION ALL性能更好，这可能和使用GROUPING SETS只需要访问一次表有关。

二、Group by 的高阶用法

ROLLUP	GROUP BY ROLLUP(A,B,C)	首先对(A,B,C)进行GROUP BY，然后对(A,B)进行GROUP BY，然后是(A)进行GROUP BY， 最后对全表进行GROUP BY操作
CUBE	GROUP BY  CUBE(A,B,C)	首先对(A,B,C)进行GROUP BY，然后依次对(A,B)、(A,C)、(A)、(B,C)、(B)、(C)进行GROUP BY，最后对全表进行GROUP BY操作。
GROUPING SETS	GROUP BY  GROUPING SETS(A,B,C)	依次对(C)、(B)、(A)进行GROUP BY。

三、实例

select  (case when left(pt_type,3) ='746' then '玻璃基板'  
              when pt_prod_line in ('1002','1004','1005')  and (pt_pm_code ='P' or ld_char1 like '%外购%') then '原厂屏' 
              when pt_prod_line in ('1002','1004','1005') and pt_pm_code  ='M' then  'KTC屏'
              when pt_type  ='9043'  then  'TCOM板' else '成品'  end ) mt_type, 
      (case  when ld_part like 'KTC-74%' then ld_char1 when left(pt_type,2) ='90'  and left(pt_prod_line,2) ='90'  then pt_size      
             when  pt_tcom = 1  and pt_pm_code ='M'   then gend_name||'(独立TCON)' else gend_name end ) pt_size,
      ld_site,pt_part,pt_desc1, ld_loc,loc_desc,sum(ld_qty_oh) as ld_qty_oh 
from ld_det a 
join pt_mstr b on a.ld_part = b.pt_part
left join gend_det on gend_option = pt_type and gend_gen ='SRC_CAT'
left join loc_mstr on loc_loc = ld_loc          
where ld_qty_oh <> 0      and
      ld_loc not like '%SP03%' /*and  
 ( ( :var_screen_p = 1 and left(pt_type,3) in ('741','742')   and (pt_pm_code ='P' or ld_char1 like '%外购%'))  or 
   ( :var_screen = 1  and  left(pt_type,3) in ('741','742') and pt_pm_code ='M')  or 
   (  :var_glass =1   and left(pt_type,3) ='746')  or 
  (  :var_tcom =1 and pt_type ='9043')  or 
   ( :var_p =1    and   left(pt_prod_line,2) = '90') or 
   ( :var_other = 1 )) */
  and right(ld_part,2)='-F'
  group by grouping sets ( (ld_site,ld_loc,
                           (case when left(pt_type,3) ='746' then '玻璃基板'  
                                 when pt_prod_line in ('1002','1004','1005')  and (pt_pm_code ='P' or ld_char1 like '%外购%')  then '原厂屏' 
                                 when pt_prod_line in ('1002','1004','1005') and pt_pm_code  ='M' then  'KTC屏'
                                 when pt_type  ='9043'  then  'TCOM板' else '成品'  end ),
                    (case when ld_part like 'KTC-74%' then ld_char1 when left(pt_type,2) ='90'  and left(pt_prod_line,2) ='90'  then pt_size  
                     when  pt_tcom = 1  and pt_pm_code ='M'  then gend_name||'(独立TCON)' else   gend_name end ) ,pt_part,pt_desc1,loc_desc),
                       (ld_site,
                       (case when left(pt_type,3) ='746' then '玻璃基板'  
                             when pt_prod_line in ('1002','1004','1005')  and (pt_pm_code ='P' or ld_char1 like '%外购%')  then '原厂屏' 
                             when pt_prod_line in ('1002','1004','1005') and pt_pm_code  ='M' then  'KTC屏'
                             when pt_type  ='9043'  then  'TCOM板'else '成品'  end ),
                       (case when left(pt_type,2) ='90'  and left(pt_prod_line,2) ='90'  then pt_size  
                             when  pt_tcom = 1 and pt_pm_code ='M'  then gend_name||'(独立TCON)' else gend_name end)),
               ((case when left(pt_type,3) ='746' then '玻璃基板'  
                      when pt_prod_line in ('1002','1004','1005')  and (pt_pm_code ='P' or ld_char1 like '%外购%')  then '原厂屏' 
                      when pt_prod_line in ('1002','1004','1005') and pt_pm_code  ='M' then  'KTC屏'
                      when pt_type  ='9043'  then  'TCOM板' else '成品'  end),
              (case  when ld_part like 'KTC-74%' then ld_char1 
                     when left(pt_type,2) ='90'  and left(pt_prod_line,2) ='90'  then pt_size  
                     when  pt_tcom = 1   and pt_pm_code ='M' then gend_name||'(独立TCON)' else gend_name end)))           
order by  mt_type,pt_size

　　

转载于:https://www.cnblogs.com/su1643/p/6292018.html