习题脚本:
/*题目:要求在每组order_book内,按日期升序排列(order_no排序不管),常规分组在前,小计在后,合计最后。
根据结果可以看出,对于book1来说,前4行就是标准分组,即*/
DROP TABLE t;
CREATE TABLE t(ORDER_date DATE,--订购日期
order_no NUMBER,--订购号
order_book VARCHAR2(10),--订购书籍
order_fee NUMBER,--订单总金额
order_num NUMBER--订单明细数目
);
INSERT INTO t
SELECT DATE '2010-5-1'+LEVEL,
TRUNC(dbms_random.value*1000),
'book1',100*LEVEL,LEVEL
FROM dual
CONNECT BY LEVEL<5;
INSERT INTO t
SELECT DATE '2010-6-1'+LEVEL,
TRUNC(dbms_random.value*1000),
'book2',200*LEVEL,LEVEL
FROM dual
CONNECT BY LEVEL<5;
COMMIT;
/*题目:要求在每组order_book内,按日期升序排列(order_no排序不管),常规分组在前,小计在后,合计最后。
根据结果可以看出,对于book1来说,前4行就是标准分组,即*/
SELECT t.order_date
,t.order_no
,t.order_book
,SUM(t.order_fee) sum_order_fee
,SUM(t.order_num) sum_order_num
FROM t
GROUP BY t.order_book,t.order_date,t.