当前位置:操作系统 > Unix/Linux >>

[每日一题] OCP1z0-047 :2013-07-31扩展GROUP BY分组――ROLLUP

[每日一题] OCP1z0-047 :2013-07-31扩展GROUP BY分组――ROLLUP
 
 
我们都知道group by能用来分组的子句;扩展了的group by加上rollup子句,就能为每一个分组返回一条小计录,并为全部分组返回总记录,操作如下:
[html] 
gyj@OCM> WITH t AS  
  2   (SELECT 'A' comp,'x' cate,'o' oth,100 fee FROM dual UNION ALL  
  3    SELECT 'A' comp,'y' cate,'m' oth,200 fee FROM dual UNION ALL  
  4   SELECT 'B' comp,'x' cate,'o' oth,300 fee FROM dual UNION ALL  
  5   SELECT 'B' comp,'y' cate,'m' oth,100 fee FROM dual)  
  6   SELECT comp,cate,oth,SUM(fee)  
  7   FROM t  
  8   GROUP BY comp,ROLLUP((cate,oth));  
  
C C O   SUM(FEE)  
- - - ----------  
A x o        100  
A y m        200  
A            300  
B x o        300  
B y m        100  
B            400  
  
6 rows selected.  

 

 
答案A是说只找每行小,这个应该是grouping sets,操作如下:
 
[html] 
gyj@OCM> WITH t AS  
  2   (SELECT 'A' comp,'x' cate,'o' oth,100 fee FROM dual UNION ALL  
  3    SELECT 'A' comp,'y' cate,'m' oth,200 fee FROM dual UNION ALL  
  4   SELECT 'B' comp,'x' cate,'o' oth,300 fee FROM dual UNION ALL  
  5   SELECT 'B' comp,'y' cate,'m' oth,100 fee FROM dual)  
  6   SELECT comp,cate,oth,SUM(fee)  
  7   FROM t  
  8   GROUP BY comp,GROUPING SETS((cate,oth));  
  
C C O   SUM(FEE)  
- - - ----------  
A y m        200  
B x o        300  
B y m        100  
A x o        100  

 

 
答案B是说每组的合计,肯定不对了。
 
答案C看到一个right toleft(从右到左),因为rollup(a,b,c)是按照这顺序的先group by a,b,c然后a,b,然后a,然后不分组,从右到左4个结果unionall。
 
答案D是说组合所有可能,那应该是CUBE。
[html] 
gyj@OCM> WITH t AS  
  2  (SELECT 1 a,2 b,3 c,4 d,5 e,6 f,7g,8 h   
  3  FROM dual)  
  4  SELECT   
  5  decode(GROUPING(a),1,0,1) a,  
  6  decode(GROUPING(b),1,0,1) b,  
  7  decode(GROUPING(c),1,0,1) c,  
  8  decode(GROUPING(d),1,0,1) d,  
  9  decode(GROUPING(e),1,0,1) e,  
 10  decode(GROUPING(f),1,0,1) f,  
 11  decode(GROUPING(g),1,0,1) g,  
 12  decode(GROUPING(h),1,0,1) h  
 13  FROM t  
 14  GROUP BY CUBE(a,b,c,d,e,f,g,h);  
  
         A          B          C          D          E          F          G          H  
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------  
         0          0          0          0          0          0          0          0  
         0          0          0          0          0          0          0          1  
         0          0          0          0          0          0          1          0  
         0          0          0          0          0          0          1          1  
         0          0          0          0          0          1          0          0  
         0          0          0          0          0          1          0          1  
         0          0          0          0          0          1          1          0  
         0          0          0          0          0          1          1          1  
         0          0          0          0          1          0          0          0  
         0          0          0          0          1          0          0          1  
         0          0          0          0          1          0          1          0  
         0          0          0          0          1          0          1          1  
         0          0          0          0          1          1          0          0  
         0          0          0          0          1          1          0          1  
         0          0          0          0          1          1          1          0  
         0          0          0          0          1          1          1          1  
         0          0          0          1          0          0          0          0  
         0          0          0          1          0          0          0          1  
         0          0          0          1          0          0          1          0  
省略结果。。。。。。  
1          1          1          1          1          1          0          0  
         1          1          1          1          1          1          0          1  
         1          1          1          1          1          1          1          0  
         1          1          1          1          1          1          1          1  
  
256 rows selected.  
正确答案是:C
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,