# 047-020

20. In which scenario would you use the ROLLUP operator for expression or
columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B to create group-wrise grand totals for the groups specified within a GROUP
BY clause
C. to create a grouping for expressions or columns specified within a GROUP
BY clause in one direction,from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP
BY clause in all possible directions, which is cross-tabular report for
calculating the subtotals
Calculate:计划 subtotal:求..部分和 direction:用法 cross:交叉tabula:列表
The CUBE operation is something of a threedimensional version of ROLLUP. CUBE goes
beyond the functionality of ROLLUP by calculating subtotals for every possible
grouping within the columns selected and grouped. The CUBE is part of the GROUP BY
and as such is parsed as part of the GROUP BY clause within the overall execution of
the SELECT statement.
Rollup,cube子句在ORACLE中可以用来做交叉报表，如：如总计，小计类的功能很容易实

Oracle的Cube,Rollup子句的用法产生交叉报表的情况，演示一个cube的例子
create table test(sales varchar2(10),dest varchar2(10),revenue number);
insert into test values('smith','hangzhou',1000);
insert into test values('smith','wenzhou',2000);
insert into test values('allen','wenzhou',3000);
insert into test values('allen','wenzhou',4000);
SALES DEST REVENUE
---------- ---------- ----------
smith hangzhou 1000
smith wenzhou 2000
allen wenzhou 3000
allen wenzhou 4000

------- ------------ ----------- ---------
allen 7000 7000
smith 1000 2000 3000
1000 9000 10000

select sales,dest,sum(revenue) from test group by cube(sales,dest);
SALES DEST SUM(REVENUE)
---------- ---------- ------------
10000 所有sales的总销售收入
wenzhou 9000 所有sales在温州的销售收入
hangzhou 1000 所有sales在杭州的销售收入
allen 7000 allen的所有销售收入
allen wenzhou 7000 allen在温州的销售收入
smith 3000 smith所有的销售收入
smith wenzhou 2000 smith在温州的销售收入
smith hangzhou 1000 smith在杭州的销售收入
8 rows selected.

hangzhou wenzhou
allen 7000
7000
smith 1000 2000
3000
10000
select sales,dest,sum(revenue) from test group by rollup(sales,dest)
SALES DEST SUM(REVENUE)
---------- ---------- ------------------
allen wenzhou 7000 allen在温州的销售收入
allen 7000 allen的所有销售收入
smith wenzhou 2000 smith在温州的销售收入
smith hangzhou 1000 smith在杭州的销售收入
smith 3000 smith 所有的销售收入
10000 所有sales的总销售收入

• 博文量
416
• 访问量
204201