在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.