group by扩展函数的习题（读书笔记）

来源：互联网发布：想给淘宝店铺取个名字编辑：程序博客网时间：2024/05/16 00:24

习题脚本:

DROP TABLE t;CREATE TABLE t(ORDER_date DATE,--订购日期               order_no NUMBER,--订购号               order_book VARCHAR2(10),--订购书籍               order_fee  NUMBER,--订单总金额               order_num  NUMBER--订单明细数目               );INSERT INTO tSELECT DATE '2010-5-1'+LEVEL,TRUNC(dbms_random.value*1000),'book1',100*LEVEL,LEVELFROM dualCONNECT BY LEVEL<5;INSERT INTO tSELECT DATE '2010-6-1'+LEVEL,TRUNC(dbms_random.value*1000),'book2',200*LEVEL,LEVELFROM dualCONNECT 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_numFROM tGROUP BY t.order_book,t.order_date,t.order_noORDER 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_numFROM tGROUP BY t.order_bookORDER 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_numFROM tGROUP 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 tGROUP 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_numFROM tGROUP BY ROLLUP(t.order_book,(t.order_date,t.order_no))ORDER BY t.order_book,t.order_date

0 0