[每日一题] 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.
答案Ａ是说只找每行小，这个应该是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
答案Ｂ是说每组的合计，肯定不对了。
答案Ｃ看到一个right toleft（从右到左），因为rollup(a,b,c)是按照这顺序的先group by a,b,c然后a,b,然后a,然后不分组，从右到左4个结果unionall。
答案Ｄ是说组合所有可能，那应该是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