oracle的group by rollup查詢結構

在047考題中有以下這麼一道考題

11.View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.

You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by

QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as

for a PRODUCT_ID for the last six months.

Which SQL statement would you execute to get the desired output?

A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"

FROM order_items oi JOIN orders o

ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6;

B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"

FROM order_items oi JOIN orders o

ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id,oi.product_id) HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6;

C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"

FROM order_items oi JOIN orders o

ON oi.order_id=o.order_id GROUP BY ROLLUP (o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6;

D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"

FROM order_items oi JOIN orders o

ON oi.order_id=o.order_id WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 GROUP BY ROLLUP (o.customer_id, oi.product_id) ;

Answer: D

上面這道題中其實要選擇正確的答案是很簡單的，就是group by應該放在where的後面，但是題目 中出現的一個知識點需要我們注意，就是rollup的使用。

大家都熟悉group by基本文法，例如要統計每個部門的員工最高的工資，就可以使用

select max(sal) from emp group by deptno;

但是如果出現比較複雜的統計，單單靠group by基本用法是不能滿足的需求的，還得配合rollup一 起來使用，例如：

需要安裝職位，經理，部門來統計員工的總工資，並且對職位，經理做一個子統計

SQL> select job,mgr,deptno,sum(sal) from emp where deptno in(10,30) group by rollup(job,mgr,deptno);

JOB        MGR  DEPTNO SUM(SAL)

--------------------------- ---------- ---------- ----------

CLERK        7698   30  950

CLERK        7698     950

CLERK        7782   10  1300

CLERK        7782     1300

CLERK             2250

MANAGER       7839   10  2450

MANAGER       7839   30  2850

MANAGER       7839     5300

MANAGER             5300

SALESMAN       7698   30  5600

SALESMAN       7698     5600

JOB        MGR  DEPTNO SUM(SAL)

--------------------------- ---------- ---------- ----------

SALESMAN            5600

PRESIDENT          10  5000

PRESIDENT            5000

PRESIDENT            5000

             18150

16 rows selected.