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.order_no
ORDER BY order_book,order_date
/*第5行小计可以看做是group BY order_book,然后横跨order_time,order_no列的小计,所以也就是*/
SELECT NULL
,NULL
,t.order_book
,SUM(t.order_fee) sum_order_fee
,SUM(t.order_num) sum_order_num
FROM t
GROUP BY t.order_book
ORDER BY order_book
/*根据上面两点,可以想到组合列分组,ROLLUP(t.order_book,(t.order_date,t.order_no)),排序是ORDER BY order_book,order_date
即*/
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 ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date
/*最后结合grouping_id来分组*/
SELECT t.order_date
,t.order_no
,t.order_book
,SUM(t.order_fee) sum_order_fee
,SUM(t.order_num) sum_order_num
,grouping_id(t.order_date,t.order_no,t.order_book)
FROM t
GROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date
----------------结合上面的结果,使用decode进行报表格式化
SELECT decode(grouping_id(t.order_date,t.order_no,t.order_book),6,order_book||'小计',
7,'合计',
to_char(t.order_date,'yyyy-mm-dd')) order_date
,t.order_no
,decode(grouping_id(t.order_date,t.order_no,t.order_book),0,order_book,
NULL) rder_book
,SUM(t.order_fee) sum_order_fee
,SUM(t.order_num) sum_order_num
FROM t
GROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))
ORDER BY t.order_book,t.order_date