RollUP

如果想要得到每个地区每个月的销售金额，可以使用Group By ，如下：
SELECT R.NAME REGION,

       TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

GROUP BY R.NAME, O.MONTH;
但是有的时候我们希望能同时得到每个月所有地区的总金额，通常会使用Union：
SELECT R.NAME REGION,

       TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

GROUP BY R.NAME, O.MONTH

UNION ALL

SELECT R.NAME REGION, NULL, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

GROUP BY R.NAME

UNION ALL

SELECT NULL, NULL, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID;
但是一般使用Union的情况下，察看执行计划会发现消耗较大。
这个时候就轮到ROLLUP出场了：

SELECT R.NAME REGION,

       TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

GROUP BY ROLLUP (R.NAME, O.MONTH);

当然根据需求的不同可以使用如下：

SELECT O.YEAR, TO_CHAR(TO_DATE(O.MONTH, 'MM'), 'Month') MONTH,

R.NAME REGION, SUM(O.TOT_SALES)

FROM ORDERS O, REGION R

WHERE R.REGION_ID = O.REGION_ID

AND O.MONTH BETWEEN 1 AND 3

GROUP BY O.YEAR, O.MONTH ROLLUP (R.NAME);

局部RollUp